Overlapping Times

[KoopLaFez]

Ok, I have a table that stores information on events. It has a Date field for the day the event takes place and a StartTime and an EndTime field that track when the event starts and ends.

Now, I wrote a simple SQL statement that gives me the total hours for each event by subtracting the StartTime from TheEndTime, and then another statement which adds up these totals. However, my problem is this. When events overlap, I don't want to count the hours for both events over the time where they overlap. I only want to count the hours where one ore more events are occuring, so that there can only be a max of 24 hours, regardless of how many simultaneous events are occuring in the day.

I am really confused at how to represent this in SQL though, and I'm beginning to think that SQL can't handle this sort of calculation and I'm going to have to go through each record programatically. I would really like to avoid doing this though, so if anyone has any suggestions, please let me know. They will be greatly appreciated

 

[jimbopalmer]

Select Max(endtime) - Min(starttime) from Mytable where Date = '07-MAY-2002'

Would return the maximum possible elaped time, but would not prevent gaps in events I tried to remain child-like, all I acheived was childish.

 

[KoopLaFez]

Yeah, I got to that point, but I can't allow gaps in the vents to be counted along with the event hours. This is where I started scratching my head and thinking that I can't do this just with SQL

 

[karluk]

Your best hope of doing this easily is to calculate the length of all the unscheduled periods during the day and then subtracting the result from 24 hours. The start of an unscheduled period is either midnight or an EndTime from your table for which there is no other row in your table with a start time <= EndTime and end time > EndTime. The end of the unscheduled period is either the next midnight or the earliest StartTime from your table which is > than the unscheduled period's start.

 

[rspooner]

Can't see all of it yet, but you need to compare records in the table against other records, so you need a self-join. You need a primary key (eventid) to prevent a row from joining itself. The same table must be listed twice in the FROM clause, and you do the join with set of conditions to check for overlaps.

So to get a list of the earliest starttime for a given day with the endtime of an overlapping event, and the corresponding duration, I think you need to do the following (with a little pseudo-code):

SELECT e2.starttime AS start, 
       MAX(e1.endtime) as end, -- end of continuous period
       (e1.endtime - e2.starttime) AS duration
FROM  Events e1, Events e2
WHERE e1.eventid <> e2.eventid -- stop row joining to self
AND   e2.date = 'dd-MMM-yy'
AND   e1.date = e2.date
AND   e1.starttime BETWEEN e2.starttime AND e2.endtime
AND   e2.endtime   BETWEEN e1.starttime AND e1.endtime
GROUP BY e2.starttime

Hope this starts you down the right path...