# Get total amount of time between records when changed state

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
)
``````

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.

• 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 Jun 30 '18 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 R Jun 30 '18 at 13:30
• Is it possible to make this kind of calculations with MS SQL? – Gyuzal R Jun 30 '18 at 14:03
• Yes it is, I've done something similar before. If I have time later I'll try to work out the details. – Scott M Jun 30 '18 at 14:40
• @ScottM, I would really really appreciate, since it's super urgent for me – Gyuzal R Jun 30 '18 at 14:56

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

• 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 R Jul 1 '18 at 8:41
• @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 Jul 1 '18 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 R Jul 1 '18 at 11:11

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