select overlapping datetime events with SQL

Question

Hi. I have a SQL table Events (ID int, Event int, StartTime datetime, Duration int).

Event is event code (1=system running, 2=break)

Duration is the amount of seconds that the event was active.

I'd like to get the amount of seconds that event 1 was active, but subtract the duration of event 2.

E.g. event 1 was from 1:00 to 6:00, event 2 from 0:00 to 2:00 and event 2 from 5:00 to 6:00. The total time should be from 2:00 to 5:00 -> 3 hours.

There is a way I can think of: for each event 1 find all events 2 that can intersect with event 1, and for each event 2 in that set: trim its duration to get only the part that was active during its event 1.

e.g. for my event 1 (1:00 - 6:00) I'll find event 2 (0:00 - 2:00), get only the part that interests me (1:00-2:00); find another event 2(5:00-6:00), get the part that interests me (it's whole event 5:00-6:00) - that summed up are two hours. The total time of event 1 was 5 hours; 5 hrs - 2 hrs (event 2) is 3 hours.

But this won't work if there are thousands of events in the specified time frame, so I'd prefer a hint of solution without loops (cursors).

Answer 1

The simplest way I can think of to do this is with multiple self-joins. I say multiple because the Event 2 can start before or during Event 1.

Here's a bit of code that will answer your question if Event 2 always starts before Event 1.

select DateDiff(s,e1.StartTime, DateAdd(s,e2Before.Duration,e2Before.StartTime)) 
from events e1
join events e2Before
    on (e1.StartTime between e2Before.StartTime and DateAdd(s,e2Before.duration,e2Before.StartTime))
    and e1.event = 1
    and e2Before.event = 2

To answer the question fully, you'll need to add another join with some of the DateAdd parameters swapped around a bit to cater for situations where Event 2 starts after Event 1 starts.

Answer 2

;WITH CTE AS (
SELECT evnt2.id as ID, sum(evnt1.duration) as Duration from #events evnt1
INNER JOIN #events evnt2
ON evnt1.id <> evnt2.id
WHERE DATEADD(second, evnt1.duration, evnt1.starttime)
BETWEEN evnt2.starttime AND DATEADD(second, evnt2.duration, evnt2.starttime)
GROUP BY evnt2.id) 
SELECT #events.duration - CTE.duration, * FROM #events INNER JOIN
CTE ON #events.id = CTE.id