CODEWITHSUNDEEP
sqlsql-server
Herin Domadia
Herin Domadia

Reputation: 1

SQL Query outage occurrence time and duration

I am new to SQL and would appreciate if anyone can provide some assistance. I am trying to run a SQL Query which can provide the outage occurrence time and duration based on the samplevalue. Samplevalue =1 is up & Samplevalue = 0 is Down.

link to the table

From above table we have to calculate how much time a particular device is with samplevalue = 0 (down)

Would like to get an output example below

output

Upvotes: 0

Views: 198

Answers (1)

Ajan Balakumaran
Ajan Balakumaran

Reputation: 1649

This code is not checked for syntax errors, but this is the logic for you to imply, Datediff is a sql server function or you can use similar for datediff in other engines.

SELECT target,samplevalue,min(sampletime) as StartTime,max(sampletime) as EndTime,datediff(minute,max(sampletime),min(sampletime)) as Outrage
FROM table 
WHERE samplevalue=0
GROUP BY target,samplevalue

Upvotes: 1

Related Questions