My Problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

My Problem

Author

Topic

prompratan
Starting Member

30 Posts

Posted - 2004-12-21 : 23:04:04

Hippi
Yak Posting Veteran

63 Posts

Posted - 2004-12-21 : 23:11:06

quote:
Originally posted by prompratan

What is your problem???

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-12-21 : 23:14:24

well obviously it is a missing image link

Corey

prompratan
Starting Member

30 Posts

Posted - 2004-12-22 : 01:05:22

I want write SQL Language for
Select data from Raw_Data table insert into Result table

hgorijal
Constraint Violating Yak Guru

277 Posts

Posted - 2004-12-22 : 01:24:51

wel...


INSERT INTO Result_table (<column list...>)
   SELECT <column list...> from Raw_Data

Hemanth Gorijala
Exchange a Dollar, we still have ONE each._______Exchange an Idea, we both have TWO each.

prompratan
Starting Member

30 Posts

Posted - 2004-12-22 : 01:34:09

Thank you hgorijal. But
INSERT INTO Result_table (<column list...>)
SELECT <column list...> from Raw_Data

I wrote...
Insert into Result_table (emp_id,date,time_in_work,time_out_lunch,time_in_lunch,time_out_work)
Select
emp_id,
date,
<time_in_work>,
<time_out_lunch>,
<time_in_lunch>,
<time_out_work>
from Raw_data

I should write <time_in_work>,<time_out_lunch>,<time_in_lunch>,<time_out_work> ???

I am sorry. I stupid English language.

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2004-12-22 : 02:23:19

No <> were used to highlight a list of values like in BOL

Your query should read

INSERT Result_table (emp_id, [date], time_in_work, time_out_lunch, time_in_lunch, time_out_work)
SELECT emp_id, [date], time_in_work, time_out_lunch, time_in_lunch, time_out_work
FROM Raw_data

date is a reserved word so needs to be encapsulated by []

Andy

prompratan
Starting Member

30 Posts

Posted - 2004-12-22 : 03:40:34

Thank you for help but I mean <time_in_work>,<time_out_lunch>,<time_in_lunch>,<time_out_work>

I will write .... for time_in_work by condition min(4.00-11.00)
I will write .... for time_in_work by condition min(11.01-12.30)
I will write .... for time_in_work by condition max(12.31-14.00)
I will write .... for time_in_work by condition max(14.01-23.59)

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2004-12-22 : 04:17:32

Im not sure i understand what you are trying to achieve but i am guessing that "time_in_work" repeated 4 times is a typo and should refer to different columns time_in_work, time_out_lunch etc

SELECT emp_id, CONVERT(varchar(10),[date],103), MIN(time_in_work), MIN(time_out_lunch), MAX(time_in_lunch), MAX(time_out_work)
FROM Raw_data
GROUP BY emp_id, CONVERT(varchar(10),[date],103)

I'm probably totally wrong in guessing what you are trying to achieve so you should post sample data and what you expect to achieve

Andy

prompratan
Starting Member

30 Posts

Posted - 2004-12-22 : 04:27:55

Thank you very much Andy

prompratan
Starting Member

30 Posts

Posted - 2004-12-22 : 04:36:34

SELECT emp_id, CONVERT(varchar(10),[date],103), MIN(time_in_work), MIN(time_out_lunch), MAX(time_in_lunch), MAX(time_out_work)
FROM Raw_data
GROUP BY emp_id, CONVERT(varchar(10),[date],103)

Really!!!
MIN(time_in_work) is use MIN(time) because source table have Time column only
MIN(time_out_lunch) is use MIN(time) because source table have Time column only
MAX(time_in_lunch) is use MAX(time) because source table have Time column only
MAX(time_out_work) is use MAX(time) because source table have Time column only

But if I write this statement then I will get MIN(time_in_work) = MIN(time_out_lunch) and MAX(time_in_lunch) = MAX(time_out_work)

I don't known. I can use MIN(time_in_work) have condition for select data by 4.00-11.00 only
MIN(time_out_lunch) have condition for select data by 11.01-12.30 only
MAX(time_in_lunch) have condition for select data by 12.31-14.00 only
MAX(time_out_work) have condition for select data by 14.01-23.59 only

This is last question. Thank you for every answer for me.

hgorijal
Constraint Violating Yak Guru

277 Posts

Posted - 2004-12-22 : 05:23:02

I wish I understand what you are saying.....

Post the table DDL (create table statement) for the "Raw_Data".
Then, explain, in very simple english, the result-set you are expecting...

Hemanth Gorijala
Exchange a Dollar, we still have ONE each._______Exchange an Idea, we both have TWO each.

prompratan
Starting Member

30 Posts

Posted - 2004-12-22 : 05:44:04

/*Raw_Data Table*/
CREATE TABLE [dbo].[Raw_Data] (
[emp_id] [int] NULL ,
[date] [datetime] NULL ,
[time_in_out] [datetime] NULL
) ON [PRIMARY]
GO

/*Result Table*/
CREATE TABLE [dbo].[Result] (
[emp_id] [int] NOT NULL ,
[date] [datetime] NOT NULL ,
[time_in_work] [datetime] NULL ,
[time_out_lunch] [datetime] NULL ,
[time_in_lunch] [datetime] NULL ,
[time_out_work] [datetime] NULL
) ON [PRIMARY]
GO

I want select data from Raw_data Table insert into Result Table by
Result.emp_id = Raw_data.emp_id
Result.date = Raw_data.date
Result.time_in_work = MIN(Raw_data.time_in_out) /*I have condition time_in_out between 04:00-11:00"*/
Result.time_out_lunch = MIN(Raw_data.time_in_out) /*I have condition time_in_out between 11:01-12:30"*/
Result.time_in_lunch = MAX(Raw_data.time_in_out) /*I have condition time_in_out between 12:31-14:00"*/
Result.time_out_work = MAX(Raw_data.time_in_out) /*I have condition time_in_out between 14:01-23:59"*/
I hope Hemanth Gorijala undenstand
thank you Hemanth Gorijala

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2004-12-22 : 05:59:55

Try, this is only an example to get you started and you will need to change it to meet your needs for each scenario

MIN(CASE WHEN DATEPART(hh,time_in_out) BETWEEN 4 AND 11 THEN time_in_out END) AS time_in_work

Andy

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-12-22 : 10:32:39

All that from a red X?

prompratan
Starting Member

30 Posts

Posted - 2004-12-22 : 20:20:26

Thank you very much Andy

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources