Calculate time duration

Question

I have a table with status_id and time column. Need to calculate time duration of status=1.

Sample data like this:

CREATE TABLE #Temp (Id int, status_id int, [Time] DateTime);
INSERT INTO #Temp  VALUES
(1, 0, '2023-10-13 05:23:19.433'),
(1, 1, '2023-10-13 08:32:45.230'),
(1, 0, '2023-10-13 10:04:29.470'),
(1, 0, '2023-10-13 10:53:54.527'), 
(2, 1, '2023-10-13 11:32:39.213'), 
(2, 1, '2023-10-13 12:14:10.837'), 
(2, 0, '2023-10-13 16:02:10.117'), 
(2, 0, '2023-10-13 17:55:10.380')

For example, the duration of these two records is 92 minutes.

(1, 1, '2023-10-13 08:32:45.230'),

(1, 0, '2023-10-13 10:04:29.470'),

The final result should be:

id sum_diff

1 92

2 270

Answer 1

Hi @Lylyy

Try this query:

;WITH CTE AS
(
 SELECT *,LAG(status_id) OVER(PARTITION BY id ORDER BY [time]) Lag_status,
          LAG(time)OVER(PARTITION BY id ORDER BY [time]) Lag_time
 FROM #Temp 
)
SELECT Id, SUM(DATEDIFF(minute, Lag_time, [time])) sum_diff
FROM  CTE
WHERE (status_id = 0 and Lag_status = 1)OR(status_id = 1 and Lag_status = 1)
GROUP BY Id

Best regards,

Cosmog Hong