Search code examples
sqlsql-server

Query to count time difference when its greater than a minimum time

I am using SQL Server. I am trying to count all of the down time that we have had on one of our machines.

We currently have a SQL table set up to record datetime (and other metrics) of every part that is run on the machine. I am trying to write something that would compare each line to the next closest. If the difference is greater than 5 minutes, I would like to add that difference to a count. Then I want to know the total count.

EXAMPLE:

Column A DateTime
Cell 1 1:05:00
Cell 2 1:06:00
Cell 3 1:08:00
Cell 4 1:18:00
Cell 5 1:30:00
Cell 6 1:32:00

The count would be 22 minutes because the time between cell 3 and cell 4 is greater than 5 minutes so it would count that, and the difference between cell 4 and 5 is greater than 5 minutes so it would count that difference in time.

So far I have looked in datediff but I am not sure if this is the right function I want to be using or not. Here is as far as I have gotten:

SELECT * 
FROM AutomatedEquipment 
WHERE DateTime = '' 
ORDER BY DateTime DESC
Solution

  • See example

    Query to calculate sum and count differences.
    We use conditional aggregation

    select 
   sum(case when dmin>5 then dmin else 0 end) sumDiff
  ,sum(case when dmin>5 then 1 else 0 end) countDiff
from(
   select *
    ,datediff(minute,DateTime,coalesce(lead(DateTime)over(order by DateTime) 
                    ,DateTime)) dmin
   from test
)b
    sumDiff countDiff
    22 2

    This query with more details

    select   -- conditional aggregation
   sum(case when dmin>5 then dmin else 0 end) sumDiff
  ,sum(case when dmin>5 then 1 else 0 end) countDiff
from( -- calculate difference in minute between current and next
   select *
     ,datediff(minute,DateTime,nextTime) dmin
   from(  -- take next time
      select *
        ,coalesce(lead(DateTime)over(order by DateTime),DateTime) nextTime
      from test
   )a
)b
    sumDiff countDiff
    22 2

    Internal subqueries and output

    select *
  ,datediff(minute,DateTime,nextTime) dmin
from(
select *
  ,coalesce(lead(DateTime)over(order by DateTime),DateTime) nextTime
from test
)a
    ColumnA DateTime nextTime dmin
    Cell 1 01:05:00.0000000 01:06:00.0000000 1
    Cell 2 01:06:00.0000000 01:08:00.0000000 2
    Cell 3 01:08:00.0000000 01:18:00.0000000 10
    Cell 4 01:18:00.0000000 01:30:00.0000000 12
    Cell 5 01:30:00.0000000 01:32:00.0000000 2
    Cell 6 01:32:00.0000000 01:32:00.0000000 0

    Main step - take DateTime value from next row

    select *
  ,coalesce(
         lead(DateTime)over(order by DateTime),  -- time from next row
         DateTime  --for last row lead value is null, we take current value
    ) nextTime
from test
    ColumnA DateTime nextTime
    Cell 1 01:05:00.0000000 01:06:00.0000000
    Cell 2 01:06:00.0000000 01:08:00.0000000
    Cell 3 01:08:00.0000000 01:18:00.0000000
    Cell 4 01:18:00.0000000 01:30:00.0000000
    Cell 5 01:30:00.0000000 01:32:00.0000000
    Cell 6 01:32:00.0000000 01:32:00.0000000

    fiddle

    DateTime functions are DBMS specific. You can adopt to your.