Help writing a SQL

[sac11585]

I have created a temp table (#TempTable) as follow...
DATE HOURS TYPE TIME_BEG TIME_END
Sep 1 2004 12:00AM 5.0 P 12.0 17.0
Sep 6 2004 12:00AM 2.0 W 0.0 0.0
Sep 6 2004 12:00AM 2.0 HP 17.0 19.0
Sep 6 2004 12:00AM 1.0 HP 19.0 20.0
Sep 6 2004 12:00AM 3.0 HP 20.0 23.0
Sep 9 2004 12:00AM 4.0 P 0.0 0.0
Sep 9 2004 12:00AM 11.0 HWO 6.0 17.0
Sep 9 2004 12:00AM 1.0 HWO 17.0 18.0

There are serveral creteria that I want before I could sum up the total hours.
1. Same date
2. Same type
3. Time_End must be equal to the Time_Beg of other record.

For example...
There are 3 records on Sep 6 2004 that matches the above creteria.
Sep 6 2004 12:00AM 2.0 HP 17.0 19.0
Sep 6 2004 12:00AM 1.0 HP 19.0 20.0
Sep 6 2004 12:00AM 3.0 HP 20.0 23.0

Therefore, I would like to group them together as follow...
Sep 6 2004 12:00AM 6.0 HP 17.0 23.0

Here is another example...
There are 2 records on Sep 9 2004 that matches the creteria.
Sep 9 2004 12:00AM 11.0 HWO 6.0 17.0
Sep 9 2004 12:00AM 1.0 HWO 17.0 18.0

Therefore, I would like to group them together as follow...
Sep 9 2004 12:00AM 12.0 HWO 6.0 18.0

I hope I have explained this clearly to all of you.
Thanks in advance!!

 

[hmckillop]

This will get the data for you (mostly)
but unfortunately not 100% certain how to manage the continous dates i.e. the end time of the previous row matching the start time of the next row (when matched on type, and date).

select date1, type, min(time_beg), max(time_end), sum(hours)
from temptable
group by date1, type

"I'm living so far beyond my income that we may almost be said to be living apart

 

[Smeat]

Try this:

SELECT [DATE], SUM(HOURS) AS HOURS, TYPE, MIN(TIME_BEG) AS TIME_BEG, MAX(TIME_END) AS TIME_END
FROM testtbl
GROUP BY [DATE], TYPE
ORDER BY
[DATE]

I tested this on a table witht he following structure:

ID int identity,
DATE datetime,
HOURS int,
TYPE nvarchar(5),
TIME_BEG money,
TIME_END money

You might need to do some casting on the results depending on the data types used in your table.

HTH

Smeat

 

[hmckillop]

smeat
is that not the same as mine?


"I'm living so far beyond my income that we may almost be said to be living apart

 

[Smeat]

hmckillop

Yes it is, I hadn't read your response when I posted mine.

Smeat