I have a table, showing the status (healthy, broken) for each device at a given timestamp. I need to get the total amount of time of a whole breakdown.
So the data is stored like this:
device_owner device_id timestamp status owner1 device_1 2001-01-01 09:00 0 owner1 device_2 2001-01-01 09:15 0 owner1 device_1 2001-01-01 09:30 1 owner1 device_2 2001-01-01 09:45 1
and so on.
The example above shows
- device_1 was down from 09:00 to 09:30 (total 30 mins),
- device_2 was down from 09:15 to 09:45 (total 30 mins)
What I need
Need to calculate the total amount of time for the whole breakdown for
owner_1 will be 45 mins (from 09:00 to 09:45) instead of 60 mins (30 + 30).
device_owner total_breakdown_min owner_1 45
What I've done
Currently I can define a breakdown time for each device separately (device_1 = 30mins, device_2 = 30mins). For this I transformed the table using
LEAD over partition by device_owner, device_id and date part from timestamp:
device_owner device_id timestamp status lead_timestamp lead_status owner1 device_1 2001-01-01 09:00 0 2001-01-01 09:30 1 owner1 device_1 2001-01-01 09:30 1 NULL NULL owner1 device_2 2001-01-01 09:15 0 2001-01-01 09:45 1 owner1 device_2 2001-01-01 09:45 1 NULL NULL
So the datetime difference between timestamp and lead_timestamp in sum gives me the total breakdown time for a device.
;WITH LeadStatus AS ( SELECT D.*, lead_status = LEAD(D.status) OVER (PARTITION BY D.device_owner, D.device_id, D.date ORDER BY D.datetime ASC), lead_timestamp = LEAD(D.datetime) OVER (PARTITION BY D.device_owner, D.device_id, D.date ORDER BY D.datetime ASC) FROM #DeviceStatus AS D ) SELECT * FROM LeadStatus
It is the same device list for any owner. The data snapshot is to be taken between 2 dates (
The records are written to a DB each n seconds and also when the device status changes.