SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table events (Time time, EventID int, Event varchar(10));
insert into events values
('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');
select * from events;


Time
EventID
Event


07:38:20.0000000
1
start

07:40:20.0000000
2
stopped

07:48:20.0000000
3
restart

07:50:20.0000000
4
aborted

08:00:20.0000000
1
start

08:40:20.0000000
5
completed

08:58:20.0000000
1
start

09:00:15.0000000
4
aborted


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 IN (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  


Time
EventID
Event
TimeDiff
CycleStartId
CycleRestartId
StopIdle
DurIndSec
DurSec
TotalWashSec
DurIdleSec


07:38:20.0000000
1
start
0
1
1
null
120
720
720
480

07:40:20.0000000
2
stopped
120
1
1
null
null
null
null
null

07:48:20.0000000
3
restart
480
1
2
480
120
null
null
null

07:50:20.0000000
4
aborted
120
1
2
null
null
null
null
null

08:00:20.0000000
1
start
600
2
3
null
2400
2400
2400
0

08:40:20.0000000
5
completed
2400
2
3
null
null
null
null
null

08:58:20.0000000
1
start
1080
3
4
null
115
115
115
0

09:00:15.0000000
4
aborted
115
3
4
null
null
null
null
null


SELECT
  *,
  CASE WHEN EventID IN (1, 3) THEN
    CONVERT(varchar(8), DATEADD(SS, 
      DATEDIFF(SS,
        MIN(Time) OVER (PARTITION BY CycleRestartId),
        MAX(Time) OVER (PARTITION BY CycleRestartId)
      )
    , '0:00:00'), 114)
  END AS DurIndSec,
  CASE WHEN EventID IN (1) THEN
    CONVERT(varchar(8), DATEADD(SS, 
      DATEDIFF(SS,
        MIN(Time) OVER (PARTITION BY CycleStartId),
        MAX(Time) OVER (PARTITION BY CycleStartId)
      )
    , '0:00:00'), 114)
  END AS DurSec,
  CASE WHEN EventId = 1 THEN 
    CONVERT(varchar(8), DATEADD(SS, 
      SUM(CASE WHEN EventId = 1 THEN 0 ELSE TimeDiff END) OVER (PARTITION BY CycleStartId)
      , '0:00:00'), 114)
  END AS TotalWashSec,
  CASE WHEN EventId = 1 THEN 
    CONVERT(varchar(8), DATEADD(SS, 
      SUM(COALESCE(StopIdle, 0)) OVER (PARTITION BY CycleStartId)
    , '0:00:00'), 114)
  END AS DurIdleSec
FROM (  
  SELECT *,
    DATEDIFF(SS, LAG(Time, 1, Time) OVER (ORDER BY Time), Time) as TimeDiff,
    SUM(CASE WHEN EventID IN (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  


Time
EventID
Event
TimeDiff
CycleStartId
CycleRestartId
StopIdle
DurIndSec
DurSec
TotalWashSec
DurIdleSec


07:38:20.0000000
1
start
0
1
1
null
00:02:00
00:12:00
00:12:00
00:08:00

07:40:20.0000000
2
stopped
120
1
1
null
null
null
null
null

07:48:20.0000000
3
restart
480
1
2
480
00:02:00
null
null
null

07:50:20.0000000
4
aborted
120
1
2
null
null
null
null
null

08:00:20.0000000
1
start
600
2
3
null
00:40:00
00:40:00
00:40:00
00:00:00

08:40:20.0000000
5
completed
2400
2
3
null
null
null
null
null

08:58:20.0000000
1
start
1080
3
4
null
00:01:55
00:01:55
00:01:55
00:00:00

09:00:15.0000000
4
aborted
115
3
4
null
null
null
null
null

Time	EventID	Event
07:38:20.0000000	1	start
07:40:20.0000000	2	stopped
07:48:20.0000000	3	restart
07:50:20.0000000	4	aborted
08:00:20.0000000	1	start
08:40:20.0000000	5	completed
08:58:20.0000000	1	start
09:00:15.0000000	4	aborted

Time	EventID	Event	TimeDiff	CycleStartId	CycleRestartId	StopIdle	DurIndSec	DurSec	TotalWashSec	DurIdleSec
07:38:20.0000000	1	start	0	1	1	null	120	720	720	480
07:40:20.0000000	2	stopped	120	1	1	null	null	null	null	null
07:48:20.0000000	3	restart	480	1	2	480	120	null	null	null
07:50:20.0000000	4	aborted	120	1	2	null	null	null	null	null
08:00:20.0000000	1	start	600	2	3	null	2400	2400	2400	0
08:40:20.0000000	5	completed	2400	2	3	null	null	null	null	null
08:58:20.0000000	1	start	1080	3	4	null	115	115	115	0
09:00:15.0000000	4	aborted	115	3	4	null	null	null	null	null