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.
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.