4

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.

Improve this question
5
  • 1
    Are you looking for all breakdowns to be combined into a single row, or is each breakdown shown separately? For example, if device_1 went down again from 11:00 to 11:30 that same day, would owner1 have one row that said 75 minutes total breakdown, or would he have two rows, one that said 45 minutes and another that said 30 minutes?
    – Scott M
    Commented Jun 30, 2018 at 13:20
  • @ScottM, it would be one row with 75 minutes. Unless any other device breaks at 11:00 till 11:45 for example.
    – Gyuzal
    Commented Jun 30, 2018 at 13:30
  • Is it possible to make this kind of calculations with MS SQL?
    – Gyuzal
    Commented Jun 30, 2018 at 14:03
  • 1
    Yes it is, I've done something similar before. If I have time later I'll try to work out the details.
    – Scott M
    Commented Jun 30, 2018 at 14:40
  • @ScottM, I would really really appreciate, since it's super urgent for me
    – Gyuzal
    Commented Jun 30, 2018 at 14:56

2 Answers 2

Reset to default
3

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

Improve this answer
3
  • Thanks for you reply, works like a charm except the case when the day starts with a device already working, you can check by adding any working device at the top
    – Gyuzal
    Commented Jul 1, 2018 at 8:41
  • 1
    @GyuzalR: Still works for me: SQL Fiddle. Note 'owner7' with 'device_7' at 07:07. It gets 0 minutes for 'owner_7'. But you can add HAVING sum(datediff(minute, maxts, mints)) <> 0 if you want to exclude results with 0 minutes;
    – sticky bit
    Commented Jul 1, 2018 at 8:48
  • ok, solved this by removing extra data. What about leading 0 status? Used the same fiddle but with repeated zero statuses at 09:01 and 09:02 link here
    – Gyuzal
    Commented Jul 1, 2018 at 11:11
1

My idea behind it , is to find the next active record ( with status = 1) that is greater in timestamp then current record . For this I used OUTER APPLY

CREATE TABLE Table1
    ([device_owner] varchar(6), [device_id] varchar(8), [timestamp] datetime, [status] int)
;

INSERT INTO Table1
    ([device_owner], [device_id], [timestamp], [status])
VALUES
    ('owner1', 'device_1', '2001-01-01 09:00:00', 0),
    ('owner1', 'device_2', '2001-01-01 09:15:00', 0),
    ('owner1', 'device_1', '2001-01-01 09:30:00', 1),
    ('owner1', 'device_2', '2001-01-01 09:45:00', 1),
    ('owner1', 'device_3', '2001-01-01 11:00:00', 0),
    ('owner1', 'device_3', '2001-01-01 11:30:00', 1)
;

-- logic it's inside CTE

;WITH myCTE
AS
(
SELECT 
    [device_owner] , [device_id] , [timestamp] , [status] 
    ,CASE WHEN T.status  = 0 THEN OA.next_ts_active ELSE NULL END as next_ts_active
FROM Table1 AS T
   OUTER APPLY  /* give me the next active position*/
   (SELECT TOP(1) timestamp as next_ts_active
    FROM Table1 as OT
    WHERE T.device_owner = OT.device_owner
         AND OT.Status = 1
         AND T.timestamp < OT.timestamp
    ORDER BY OT.timestamp ASC
   )OA -- for active
)
SELECT device_owner
    ,SUM(case when status = 0 
          then datediff(minute,timestamp,next_ts_active) 
          else 0 end) as diff_minutes
FROM myCTE
GROUP BY device_owner

Output- only the CTE

device_owner    device_id   timestamp           status  next_ts_active
owner1          device_1    01/01/2001 09:00:00 0       01/01/2001 09:30:00
owner1          device_2    01/01/2001 09:15:00 0       01/01/2001 09:30:00
owner1          device_1    01/01/2001 09:30:00 1       null
owner1          device_2    01/01/2001 09:45:00 1       null
owner1          device_3    01/01/2001 11:00:00 0       01/01/2001 11:30:00
owner1          device_3    01/01/2001 11:30:00 1       null

Then apply the difference and sum it :

device_owner    diff_minutes
owner1          75

It's 75 because I added a new device :device_3. Without it, value it's 45 . See here

Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.