clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 1811948 fiddles created (25308 in the last week).

CREATE TABLE Table1 ([device_owner] varchar(6), [device_id] varchar(8), [timestamp] datetime, [status] int) ; INSERT INTO Table1 ([device_owner], [device_id], [timestamp], [status]) VALUES ('owner1', 'device_1', '2001-01-01 09:00:00', 0), ('owner1', 'device_2', '2001-01-01 09:15:00', 0), ('owner1', 'device_1', '2001-01-01 09:30:00', 1), ('owner1', 'device_2', '2001-01-01 09:45:00', 1)--, --('owner1', 'device_3', '2001-01-01 11:00:00', 0), --('owner1', 'device_3', '2001-01-01 11:30:00', 1) ; ;WITH myCTE AS ( SELECT [device_owner] , [device_id] , [timestamp] , [status] ,CASE WHEN T.status = 0 THEN OA.next_ts_active ELSE NULL END as next_ts_active FROM Table1 AS T OUTER APPLY /* give me the next active position*/ (SELECT TOP(1) timestamp as next_ts_active FROM Table1 as OT WHERE T.device_owner = OT.device_owner AND OT.Status = 1 AND T.timestamp < OT.timestamp ORDER BY OT.timestamp ASC )OA -- for active ) SELECT device_owner ,SUM(case when status = 0 then datediff(minute,timestamp,next_ts_active) else 0 end) as diff_minutes FROM myCTE GROUP BY device_owner
device_owner diff_minutes
owner1 45
 hidden batch(es)