Grouping/min/max predicament 1

[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!!

 

[r937]

select EVENT
     , min(STARTTIME) as minStart
     , maxEnd
  from (
select EVENT
     , STARTTIME
     , max(ENDTIME)   as maxEnd
  from (
select t1.EVENT
     , t1.STARTTIME
     , t2.ENDTIME
  from placeholder as t1
inner
  join placeholder as t2
    on t1.EVENT = t2.EVENT
   and t1.STARTTIME <= t2.STARTTIME
 where not exists
       ( select *
           from placeholder
          where EVENT <> t1.EVENT
            and STARTTIME
             between t1.STARTTIME
                 and t2.STARTTIME )
       ) as dt
group
    by EVENT    
     , STARTTIME                        
       ) as dt2
group
    by EVENT   
     , maxEnd

works fine on your sample data

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[dnoeth]

select 
  min(event) 
  ,min(starttime) as starttime
  ,max(endtime) as endtime
from 
 (
  select 
    event
    ,starttime
    ,endtime
    ,(select max(starttime) 
     from placeholder p2 
     where starttime < p1.starttime
     and event <> p1.event) as dummy
  from placeholder p1
 ) dt
group by dummy
order by starttime

Dieter

 

[jcale4]

r937,

Your solution seems to work closest to what i need, however there are some differences with my table that i'd like to make modifications to your code myself (to better understand).. i've been working with your code, but i'm having a hard time understanding it. Can you briefly explain what is happening so i can understand?

Thank you!

 

[r937]

actually, i really like dieter's solution, it's way more creative and a lot easier to understand

i'm not sure i can explain mine any more succinctly than simply by suggesting that you read it from the innermost subquery outwards --

join each row to every other row for the same event with a later starting time, provided that there are no other intervening events, then take the latest endtime for the event and starttime, then take the earliest starttime for each event

but that doesn't really exlain it, does it



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)