SQL Grouping/min/max predicament

[jcale4]

FYI - I'm using DB2 v.7 as my RDBMS

I am building a scheduling system for employees. I have a table that houses "placeholders" for every 15 minute increment of the day. I need to find a creative solution for resolving the a particular day down to single events. Sound easy? OK, here's the challenge.. my records look (dumbed down) like this:

EVENT -- STARTTIME -- ENDTIME
-------------------------------
dual -- 9:00:00AM -- 9:15:00AM
dual -- 9:15:00AM -- 9:30:00AM
dual -- 9:30:00AM -- 10:00:00AM
lunch -- 10:00:00AM -- 10:15:00AM
lunch -- 10:15:00AM -- 10:30:00AM
lunch -- 10:30:00AM -- 11:00:00AM
dual -- 11:00:00AM -- 11:15:00AM
dual -- 11:30:00AM -- 11:45:00AM
meeting -- 12:00:00PM -- 12:15:00PM
meeting -- 12:15:00PM -- 12:30:00PM

... you get the point...

Somehow, i have to group those records so that i have a distinct, yet not fully distinct event to pull the min(start) and max(end) times for each... to look like this:

EVENT -- STARTTIME -- ENDTIME
-------------------------------
dual -- 9:00:00AM -- 10:00:00AM
lunch -- 10:00:00AM -- 11:00:00AM
dual -- 11:00:00AM -- 11:45:00AM
meeting -- 12:00:00PM -- 12:30:00PM

While this seems like a simple task,, it really is not.. as a matter of fact, quite frustrating.. . Can someone PLEASE help me with a solution to this problem? Seems like someone has to have been in this boat before..

Thank YOU!!

[nosepicker]

How would you know to separate the two "dual" events instead of combining them into:

EVENT -- STARTTIME -- ENDTIME
-------------------------------
dual -- 9:00:00AM -- 11:45:00AM

Is there a rule to follow, or is there another column that you can use to separate the two "dual" events?

[MAK]

Cursor

[nosepicker]

Even if you had a cursor, you would have to order the records by something. It's not clear in the example what to order the data by. The second set of "dual" records has a gap between 11:15:00 and 11:30:00. How would one know to combine those records instead of separating them?

[jcale4]

Sorry about the typo on the dual time gap.. every event record is 15 minutes between start/end.

The data should be ordered ultimately by starttime.

--- "How would you know to separate the two "dual" events instead of combining them into:" -- that is my challenge exactly, i dont know. The only difference between the events is that there is a different event between.

[MAK]

OK. Here is the solution. Assuming starttime is unique.


--drop table Mytable
use tempdb
go
create table Mytable (EVENT varchar(15),STARTTIME varchar(15),
ENDTIME varchar(15))
go
insert into Mytable select 'dual','9:00:00AM','9:15:00AM'
insert into Mytable select 'dual','9:15:00AM','9:30:00AM'
insert into Mytable select 'dual','9:30:00AM','10:00:00AM'
insert into Mytable select 'lunch','10:00:00AM','10:15:00AM'
insert into Mytable select 'lunch','10:15:00AM','10:30:00AM'
insert into Mytable select 'lunch','10:30:00AM','11:00:00AM'
insert into Mytable select 'dual','11:00:00AM','11:15:00AM'
insert into Mytable select 'dual','11:30:00AM','11:45:00AM'
insert into Mytable select 'meeting','12:00:00PM','12:15:00PM'
insert into Mytable select 'meeting','12:15:00PM','12:30:00PM'
go

select Event,min(convert(datetime,starttime)) as BeginDate,
max(convert(datetime,endtime)) as ENDDate
from (
SELECT (SELECT count(starttime) FROM Mytable x WHERE
x.starttime<= y.starttime and x.event<>y.event) AS
Sequence, event,starttime,endtime
FROM Mytable AS y
) as myt group by sequence,event order by min(starttime) desc


--result

dual 1900-01-01 09:00:00.000 1900-01-01 10:00:00.000
meeting 1900-01-01 12:00:00.000 1900-01-01 12:30:00.000
dual 1900-01-01 11:00:00.000 1900-01-01 11:45:00.000
lunch 1900-01-01 10:00:00.000 1900-01-01 11:00:00.000