sqlsql-servert-sql

Calculate duration using T-SQL

I have a table containing columns Date and PumpStatus like this:

Date            PumpStatus
---------------------------
1/1/24 0:15     Running
1/1/24 1:25     Stop
1/1/24 2:21     Running
1/2/24 12:15    Stop
1/2/24 14:15    Running

How to write a select query to calculate total running and total stop state of pump in minutes using above data between given date range?

I searched, but found no help as such. most of the posts are related to the datediff function which I'm not getting how to use in this scenario.

Solution

  • To calculate time for pump in each status (Running, stop) in minutes you can use below query.

    SELECT *
    , DATEDIFF(minute, Date, LEAD(Date) OVER (ORDER BY Date)) AS Time
FROM {table}

    This query will count the difference between two consecutive status which will give you an output like this

    01-01-24 00:15:00   |Running    |70
01-01-24 01:25:00   |Stop       |56
01-01-24 02:21:00   |Running    |45234
01-02-24 12:15:00   |Stop       |120
01-02-24 14:15:00   |Running    |null

    Now you can find the time for each status. Output time is in minutes as you want.

    DATEDIFF is a SQL Server function to find the difference in time. Parameters for DATEDIFF are:

    DATEDIFF(interval, time1, time2)

    For interval you need to pass the type of output you want, in this case it is minute.

    The LEAD() function is used to get the value from the row that succeeds the current row.

    So we used the LEAD() function to get the next rows Date value.