Find the longest duration during the machine is ON-CodePudding

I have the following table in SQL Server. I would like to find the longest duration for the machine running.

In the example above, the longest duration for the machine is ON is 2 hours using rows 5 and 6. What would be the best SQL statement that can provide the longest duration given a time range?

I have looked into the LAG Function and the LEAD Function in SQL.

CodePudding user response：

If this is really your data, you can simply use INNER JOIN and DATEDIFF:

SELECT MAX(DATEDIFF(MINUTE, T1.[DateTime], T2.[DateTime]))
FROM [my_table] T1
INNER JOIN [my_table] T2
    ON T1.[Row]   1 = T2.[Row];

CodePudding user response：

This is a gaps and islands problem, one option to solve it is to use a running sum that increased by 1 whenever a machine_on = 0, this will define unique groups for consecutive 1s followed by 0.

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

See demo