CODEWITHSUNDEEP
sqlsql-servert-sqldatediff
Nathan Studanski
Nathan Studanski

Reputation: 1

SQL sum elapsed duration between rows for values outside limit bound for each row

Bit of a SQL amateur, so stuck with translating what would be easy C++/Python/Excel function to SQL query using SQL CLR post-processing add-in from VantagePoint. Looking to do something like this excel functionality, so I'd have just the timestamp and value. Looking to do a computation for total elapsed time between readings if the value at a reading is less than a lower bound then add to that running sum, and same for a running sum for readings above a lower bound.

https://image.ibb.co/dJr0kR/SQLTime_Diff.png

This would be normal pseudocode, but unsure how it would translate into SQL with CLR

foreach(row)
    if(value < lowLimit)
        lowSum += (time[row] - time[row - 1])
    if(value > highLimit)
        highSum +=(time[row] - time[row - 1])

And then I could add to the report I'm building the final value for each sum. Purpose is to visualize/alert if the elapsed time outside limit is greater than a given acceptable range

Upvotes: 0

Views: 99

Answers (1)

digital.aaron
digital.aaron

Reputation: 5707

Given the following table:

CREATE TABLE TableA (RowDate DATETIME, val INT)

You could use the LAG() function to get the previous timestamp value. Then put that into a SUM() that only adds the delta if the val value is less than the lowLimit of 5 or greater than the highLimit of 6.

SELECT 
    lowSum = SUM(CASE WHEN val < 5 THEN DATEDIFF(minute,RowDate,LAG(RowDate) OVER (ORDER BY RowDate ASC)) END)
    ,highSum = SUM(CASE WHEN val > 6 THEN DATEDIFF(minute,RowDate,LAG(RowDate) OVER (ORDER BY RowDate ASC)) END)
FROM TableA

Upvotes: 2

Related Questions