CODEWITHSUNDEEP
sqlsql-server
Anthony
Anthony

Reputation: 33

Find the longest duration during the machine is ON

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

Row DateTime Machine On
1 9/22/2022 8:20 1
2 9/22/2022 9:10 0
3 9/22/2022 10:40 1
4 9/22/2022 10:52 0
5 9/22/2022 12:30 1
6 9/22/2022 14:30 0
7 9/22/2022 15:00 1
8 9/22/2022 15:40 0
9 9/22/2022 16:25 1
10 9/22/2022 16:55 0

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?

Desired Result:

60 minutes

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

Upvotes: 1

Views: 97

Answers (4)

John Cappelletti
John Cappelletti

Reputation: 81970

This is a classic Gaps and Islands with a little twist Adj

Example

Select Top 1
       Row1 = min(row)
      ,Row2 = max(row)+1
      ,TS1  = min(TimeStamp)
      ,TS2  = dateadd(SECOND,max(Adj),max(TimeStamp))
      ,Dur = datediff(Second,min(TimeStamp),max(TimeStamp)) + max(Adj)
 From (
        Select * 
              ,Grp = row_number() over( partition by Running order by TimeStamp) - row_number() over (order by timeStamp)
              ,Adj  = case when Running=1 and lead(Running,1) over (order by timestamp) = 0  then datediff(second,TimeStamp,lead(TimeStamp,1) over (order by TimeStamp) ) else 0 end
         From Machine_Status
      ) A
 Where Running=1
 Group By Grp
 Order By Dur Desc

Results

Row1    Row2    TS1                     TS2                       Dur
8       12      2023-01-10 08:25:30.000 2023-01-10 08:28:55.000   205

Upvotes: 1

Aaron Bertrand
Aaron Bertrand

Reputation: 280350

Here's another way that uses traditional gaps & islands methodology:

WITH src AS
(
  SELECT Island, mint = MIN([Timestamp]), maxt = MAX([Timestamp])
  FROM
  (
    SELECT [Timestamp], Island = 
      ROW_NUMBER() OVER (ORDER BY [Timestamp]) -
      ROW_NUMBER() OVER (PARTITION BY Running ORDER BY [Timestamp])
    FROM dbo.Machine_Status
  ) AS x GROUP BY Island
)
SELECT TOP (1) delta = 
   (DATEDIFF(second, mint, LEAD(mint,1) OVER (ORDER BY island)))
 FROM src ORDER BY delta DESC;

Upvotes: 2

ahmed
ahmed

Reputation: 9191

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

Upvotes: 1

gotqn
gotqn

Reputation: 43636

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];

Upvotes: 1

Related Questions