SQL Server – Calculate Total Time Between State Changes

sql servertimestamp

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 (StartDate, EndDate).
The records are written to a DB each n seconds and also when the device status changes.

Best Answer

We can use conditional aggregation and an OVER clause to get the number of broken devices for each row in devicestatus.

SELECT device_owner,
       timestamp,
       sum(CASE status
             WHEN 0
               THEN 1
             WHEN 1
               THEN -1
           END) OVER (PARTITION BY device_owner
                      ORDER BY timestamp) broken#
       FROM devicestatus;

We now use lag() to get the previous number of broken devices for each row.

SELECT device_owner,
       timestamp,
       broken#,
       lag(broken#,
           1,
           0) OVER (PARTITION BY device_owner
                    ORDER BY timestamp) previous_broken#
       FROM (SELECT device_owner,
                    timestamp,
                    sum(CASE status
                          WHEN 0
                            THEN 1
                          WHEN 1
                            THEN -1
                        END) OVER (PARTITION BY device_owner
                                   ORDER BY timestamp) broken#
                    FROM devicestatus) x1;

Now we use conditional aggregation and the OVER clause once again to get an identifier for each period where at least one device of a user was down.

SELECT device_owner,
       timestamp,
       sum(CASE
             WHEN previous_broken# = 0
               THEN 1
             ELSE
               0
           END) OVER (PARTITION BY device_owner
                      ORDER BY timestamp) broken_period#
       FROM (SELECT device_owner,
                    timestamp,
                    broken#,
                    lag(broken#,
                        1,
                        0) OVER (PARTITION BY device_owner
                                 ORDER BY timestamp) previous_broken#
                    FROM (SELECT device_owner,
                                 timestamp,
                                 sum(CASE status
                                       WHEN 0
                                         THEN 1
                                       WHEN 1
                                         THEN -1
                                     END) OVER (PARTITION BY device_owner
                                                ORDER BY timestamp) broken#
                                 FROM devicestatus) x1) x2;

From here on we can now use GROUP BY to get the minimum and maximum timestamp of the periods where at least one device of a user was down.

SELECT device_owner,
       max(timestamp) mints,
       min(timestamp) maxts
       FROM (SELECT device_owner,
                    timestamp,
                    sum(CASE
                          WHEN previous_broken# = 0
                            THEN 1
                          ELSE
                            0
                        END) OVER (PARTITION BY device_owner
                                   ORDER BY timestamp) broken_period#
                    FROM (SELECT device_owner,
                                 timestamp,
                                 broken#,
                                 lag(broken#,
                                     1,
                                     0) OVER (PARTITION BY device_owner
                                              ORDER BY timestamp) previous_broken#
                                 FROM (SELECT device_owner,
                                              timestamp,
                                              sum(CASE status
                                                    WHEN 0
                                                      THEN 1
                                                    WHEN 1
                                                      THEN -1
                                                  END) OVER (PARTITION BY device_owner
                                                             ORDER BY timestamp) broken#
                                              FROM devicestatus) x1) x2) x3
       GROUP BY device_owner,
                broken_period#;

We can now simply use datediff(), sum() and GROUP BY to calculate the total downtime for each device owner.

SELECT device_owner,
       sum(datediff(minute,
                    maxts,
                    mints)) total_breakdown_min
       FROM (SELECT device_owner,
                    max(timestamp) mints,
                    min(timestamp) maxts
                    FROM (SELECT device_owner,
                                 timestamp,
                                 sum(CASE
                                       WHEN previous_broken# = 0
                                         THEN 1
                                       ELSE
                                         0
                                     END) OVER (PARTITION BY device_owner
                                                ORDER BY timestamp) broken_period#
                                 FROM (SELECT device_owner,
                                              timestamp,
                                              broken#,
                                              lag(broken#,
                                                  1,
                                                  0) OVER (PARTITION BY device_owner
                                                           ORDER BY timestamp) previous_broken#
                                              FROM (SELECT device_owner,
                                                           timestamp,
                                                           sum(CASE status
                                                                 WHEN 0
                                                                   THEN 1
                                                                 WHEN 1
                                                                   THEN -1
                                                               END) OVER (PARTITION BY device_owner
                                                                          ORDER BY timestamp) broken#
                                                           FROM devicestatus) x1) x2) x3
                    GROUP BY device_owner,
                             broken_period#) x4
       GROUP BY device_owner;

SQL Fiddle

Related Question