Set based calculation for events table, exceptuating an event

Question

Hello, I have a table which contains events for change the state of a maintenance service. This is a sample of data:

id  date event
234 2009-04-22 10:00:00 Service Request
234 2009-04-22 10:02:00 Service Approbation
234 2009-04-22 10:03:00 Service Asignation
234 2009-04-22 10:40:00 Service Fulfilled
...

In a report, i need to show time spent on each item

id  time
234 40 minutes
235 37 minutes
...

Basically, i'm doing a DATEDIFF like this:

SELECT ...
, DATEDIFF(SECOND, 
(SELECT TOP 1 date FROM event_table ev WHERE ev.item = itm.id AND event = 1 ORDER BY date ), 
(SELECT TOP 1 date  FROM event_table ev WHERE ev.item = itm.id AND event = 4 ORDER BY date DESC)
     ), ...
FROM item_table itm
WHERE ...

Now i have to implement a new state. Delay and reactivation of a service, but these time has not to be taken into account when calculating the total ammount of time spent on that service.

id  date event
234 2009-04-22 10:00:00 Service Request
234 2009-04-22 10:02:00 Service Approbation
234 2009-04-22 10:03:00 Service Asignation
234 2009-04-22 10:07:00 Service Delayed           -- new state
234 2009-04-22 10:37:00 Service Reactivated       -- new state
234 2009-04-22 10:40:00 Service Fulfilled
...

Report

id  time
234 10 minutes -- Substract 30 minutes between Delay and Reactivation
235 26 minutes
...

The delay and reactivation can occur various times for any item.

I know I can do this with a cursor, but it seems hacky to me, is there any way to calculate this on a set-based approach?

Answer 1

The way I would do it is to first find the duration of the whole job, the subtract the durations of all delays...

Code to find the duration of delays:

SELECT
   [delay_start].event_id,
   SUM(DATEDIFF(second, [delay_start].date, [delay_finish].date))
FROM
   event_table AS [delay_start]
INNER JOIN
   event_table AS [delay_finish]
      ON  [delay_finish].event_id = [delay_start].event_id
      AND [delay_finish].date = (
                                 SELECT
                                    MIN(date)
                                 FROM
                                    event_table
                                 WHERE
                                    event_id = [delay_start].event_id
                                    AND date > [delay_start].date
                                    AND event = 'Service Reactivated'
                                )
WHERE
   [delay_start].event = 'Service Delayed'
   AND [delay_finish].event = 'Service Reactivated'
GROUP BY
   [delay_start].event_id

EDIT : A couple of typos corrected...