Determine time duration based on events without using loops

I have a table with timestamps of 5 different types of events (start, stopped, restart, aborted, and completed). The given table looks like this:

Time	EventID	Event
7:38:20	1	start
7:40:20	2	stopped
7:48:20	3	restart
7:50:20	4	aborted
8:00:20	1	start
8:40:20	5	completed
8:58:20	1	start
9:00:15	4	aborted

I would like to determine the following and display it:

• Duration of individual Wash –> From (start or restart) to (stopped or aborted or completed)
• Duration of Wash Cycle –> From (start) to (aborted or completed)
• Duration of total wash time –> Sum of all individual wash in a Wash cycle
• Duration of idle time –> Wash Cycle duration – total wash time duration

So the table should look something like the following:

Time	EventID	Event	Duration of individual Wash	Duration of Wash Cycle	Duration of total wash time	Duration of idle time
7:38:20	1	start	0:02:00	0:12:00	0:04:00	0:08:00
7:40:20	2	stopped	NULL	NULL	NULL	NULL
7:48:20	3	restart	0:02:00	NULL	NULL	NULL
7:50:20	4	aborted	NULL	NULL	NULL	NULL
8:00:20	1	start	0:40:00	0:40:00	0:01:55	0:00:00
8:40:20	5	completed	NULL	NULL	NULL	NULL
8:58:20	1	start	0:01:55	0:01:55	0:01:55	0:00:00
9:00:15	4	aborted	NULL	NULL	NULL	NULL

So far I was able to get the duration of individual Wash and the duration of Wash Cycle by joining two table (one with only start, abort, and complete; the other with all events). I am stuck on the last two columns. I’m not sure how to approach this problem efficiently without using a while loop or counter of some sort. Would love some pointers.

Here are my code so far:

SELECT IndivWash.DateTimeStamp  as 'Event TimeStamp'
    ,IndivWash.EventIDNo AS 'Event ID Number'
    ,IndivWash.EventDesc AS 'Event Description'
    -- for the duration of the WASH  ----------------------------------------------------
    ,CASE 
        WHEN (IndivWash.EventIDNo = '1' OR IndivWash.EventIDNo = '3')
            AND (LEAD(IndivWash.EventIDNo) OVER (ORDER BY IndivWash.DateTimeStamp) = '2'
                OR LEAD(IndivWash.EventIDNo) OVER (ORDER BY IndivWash.DateTimeStamp) = '4'
                OR LEAD(IndivWash.EventIDNo) OVER (ORDER BY IndivWash.DateTimeStamp) = '5')
            AND LEAD(IndivWash.EventIDNo) OVER (ORDER BY IndivWash.DateTimeStamp) <> IndivWash.EventIDNo 
        THEN 
            DATEDIFF(s, IndivWash.DateTimeStamp, LEAD(IndivWash.DateTimeStamp) OVER (ORDER BY IndivWash.DateTimeStamp))
        ELSE 
            NULL
        END AS 'Duration of individual Wash'
    -- For the duration of the CYCLE  ----------------------------------------------------
    ,CASE 
        WHEN WashCycle.EventIDNo = '1' 
            AND LEAD(WashCycle.EventIDNo) OVER (ORDER BY WashCycle.DateTimeStamp) <> WashCycle.EventIDNo 
            AND (LEAD(WashCycle.EventIDNo) OVER (ORDER BY WashCycle.DateTimeStamp) = '4' OR 
            LEAD(WashCycle.EventIDNo) OVER (ORDER BY WashCycle.DateTimeStamp) = '5')
        THEN 
            DATEDIFF(s, WashCycle.DateTimeStamp, LEAD(WashCycle.DateTimeStamp) OVER (ORDER BY WashCycle.DateTimeStamp))
        ELSE 
            NULL
        END AS 'Duration of Wash Cycle' 
    -- ----------------------------------------------------
FROM (
        --FROM: table with only start, abort and complete.
        --      to differentiate the cycles that are not aborted
        SELECT TOP (1000) DateTimeStamp
                ,EventIDNo
                ,EventDesc
        /*----------CHANGE DATABASE HERE----------*/
        FROM Washer.dbo.EventLog_vw
        /*----------------------------------------*/
        WHERE EventIDNo IN ('1','4','5')
        ORDER BY DateTimeStamp
) WashCycle
 RIGHT JOIN
(
        --FROM: table with all five events
        SELECT TOP (1000) 
                DateTimeStamp
                ,EventIDNo
                ,EventDesc
        /*----------CHANGE DATABASE HERE----------*/
        FROM Washer.dbo.EventLog_vw
        /*----------------------------------------*/
        WHERE EventIDNo IN ('1','2','3','4','5')
        ORDER BY DateTimeStamp
) IndivWash
ON WashCycle.DateTimeStamp=IndivWash.DateTimeStamp

Answer

Try this example based on precalculating cycles IDs CycleStartId and CycleRestartId:

SELECT *,
  CASE WHEN EventID IN (1, 3) THEN
      DATEDIFF(SS,
        MIN(Time) OVER (PARTITION BY CycleRestartId),
        MAX(Time) OVER (PARTITION BY CycleRestartId)
      )
  END AS DurIndSec,
  CASE WHEN EventID IN (1) THEN
      DATEDIFF(SS,
        MIN(Time) OVER (PARTITION BY CycleStartId),
        MAX(Time) OVER (PARTITION BY CycleStartId)
      )
  END AS DurSec,
  CASE WHEN EventId = 1 THEN 
      SUM(CASE WHEN EventId = 1 THEN 0 ELSE TimeDiff END) OVER (PARTITION BY CycleStartId)
  END AS TotalWashSec,
  CASE WHEN EventId = 1 THEN 
      SUM(COALESCE(StopIdle, 0)) OVER (PARTITION BY CycleStartId)
  END AS DurIdleSec
FROM (  
  SELECT *,
    DATEDIFF(SS, LAG(Time, 1, Time) OVER (ORDER BY Time), Time) as TimeDiff,
    SUM(CASE WHEN EventID = 1 THEN 1 ELSE 0 END) 
      OVER (ORDER BY Time) AS CycleStartId,
    SUM(CASE WHEN EventID IN (1, 3) THEN 1 ELSE 0 END) 
      OVER (ORDER BY Time) AS CycleRestartId,
    CASE WHEN LAG(EventId, 1, EventId) OVER (ORDER BY Time) = 2 THEN 
      DATEDIFF(SS, LAG(Time, 1, Time) OVER (ORDER BY Time), Time) 
    END AS StopIdle
  FROM events
) t

Here the reports are shown in seconds. If you need to format them as time, then you can use the following expression:

CONVERT(varchar(8), DATEADD(SS, <Int in seconds>, '0:00:00'), 114)

fiddle