By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Create Table table_name(rowid INT, datetime DATETIME, machine_on INT);
Insert Into table_name Values
(10,'2022-09-22 15:30:00.000',0),
(9,'2022-09-22 15:26:00.000',1),
(8,'2022-09-22 15:20:00.000',0),
(7,'2022-09-22 15:00:00.000',1),
(6,'2022-09-22 14:30:00.000',0),
(5,'2022-09-22 12:30:00.000',1),
(4,'2022-09-22 10:52:00.000',0),
(3,'2022-09-22 10:40:00.000',1),
(2,'2022-09-22 09:10:00.000',0),
(1,'2022-09-22 08:20:00.000',1);
10 rows affected
select top 1 datediff(minute, min([datetime]), max([datetime])) duration
from
(
select *,
sum(case when machine_on = 0 then 1 else 0 end) over (order by datetime desc) grp
from table_name
) T
group by grp
order by datediff(minute, min([datetime]), max([datetime])) desc
| duration |
|---|
| 120 |