select overlapping datetime events with SQL

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 int开发者_开发百科erests 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).

---

;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

---

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.