Combining overlapping time intervals, then summing the total time
I have a hardware group and many devices in this group. Example:
+ Room 1
|-- Computer
|-- Camera
+ Room 2
|-- Computer
|-- Switch
All devices are monitored using ping. When a device is not working, the program adds a line to the table that says the beginning of the break. When the device is turned on again, the program will update this line, indicating the end of the break.
It is good to know the total break intervals for each device. My need is to know the real cumulative time for the whole group. Example:
Group Device Start End
Room 1 Computer 2015-05-12 01:40:00 2015-05-12 01:40:20
Room 1 Camera 2015-05-12 01:40:01 2015-05-12 01:40:27
Room 2 Computer 2015-05-12 03:43:03 2015-05-12 03:46:14
Room 2 Switch 2015-05-12 03:43:00 2015-05-12 03:46:12
Room 1 Camera 2015-05-12 07:12:10 2015-05-12 07:12:22
The downtime of the real group "Room 1" is 39 seconds (NOT 58).
01:40:00 - 01:40:20 = 20 seconds
01:40:01 - 01:40:27 = 7 seconds
07:12:10 - 07:12:22 = 12 seconds
On the first two lines, see why 27 seconds and not 46 seconds:
| 00, 01, 02, 03, 04, 05, 06, 07, 08, 09, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20 |
| 01, 02, 03, 04, 05, 06, 07, 08, 09, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27 |
Well ... I have many groups and many devices for each group. How can I do this using SQL?
To help with tests ...
DECLARE @tblGroup TABLE (
id int,
name varchar(20)
)
INSERT INTO @tblGroup (id, name)
VALUES (1, 'Room 1'), (2, 'Room 2'), (3, 'Room 3'), (4, 'Room 4')
DECLARE @tblDevice TABLE (
id int,
name varchar(20),
group_id int
)
INSERT INTO @tblDevice (id, name, group_id)
VALUES (1, 'Computer', 1), (2, 'Camera', 1), (3, 'Computer', 2), (4, 'Switch', 2)
DECLARE @tblStatus TABLE (
id int,
device_id int,
dtStart datetime,
dtEnd datetime
)
INSERT INTO @tblStatus (id, device_id, dtStart, dtEnd)
VALUES (1, 1, '2015-05-12 01:40:00.0', '2015-05-12 01:40:20.0'),
(2, 2, '2015-05-12 01:40:01.0', '2015-05-12 01:40:27.0'),
(3, 3, '2015-05-12 03:43:03.0', '2015-05-12 03:46:14.0'),
(4, 4, '2015-05-12 03:43:00.0', '2015-05-12 03:46:12.0'),
(5, 2, '2015-05-12 07:12:10.0', '2015-05-12 07:12:22.0')
SELECT
s.id,
s.device_id,
g.name AS groupName,
d.name AS deviceName,
s.dtStart,
s.dtEnd
FROM @tblStatus s
INNER JOIN @tblDevice d
ON d.id = s.device_id
INNER JOIN @tblGroup g
ON g.id = d.group_id
source to share
You want to combine different groups into "islands" and calculate the extent of the islands. This is why this type of problem is sometimes referred to as gaps and islands.
Let me assume you are using SQL Server 2012+. This simplifies the calculations a bit. The idea is to define start and end for overlapping groups. Next, it is determined if the group overlaps:
select t.*,
(case when exists (select 1
from @tblstatus t2
where t2.group_id = t.group_id and
t2.dtend > t.dtstart and t2.dtstart <= t.dtstart and
t2.id < t.id
)
then 0 else 1 end) as NoOverlapBefore
from @tblstatus t
That being said, you can simply assign each row in the table the number of "NoOverlapBefore" entries that occur before it, and use the result for aggregation:
with t as (
select t.*,
(case when exists (select 1
from @tblstatus t2
where t2.group_id = t.group_id and
t2.dtend > t.dtstart and t2.dtstart <= t.dtstart and
t2.id < t.id
)
then 0 else 1 end) as NoOverlapBefore
from @tblstatus t
)
select group_id,
datediff(second, min(dtstart), max(dtend)) as total_seconds
from (select t.*,
sum(NoOverlapBefore) over (partition by group_id order by dtstart, id) as grp
from @tblstatus t
) t
group by group_id;
EDIT:
I misunderstood a few things about your data structure. SQL Fiddle is a big help. Here's the one at work that actually works.
Request:
WITH t AS (
SELECT t.*, d.group_id,
(CASE WHEN EXISTS (SELECT 1
FROM tblstatus t2 JOIN
tbldevice d2
ON d2.id = t2.device_id
WHERE d2.group_id = d.group_id AND
t2.dtend > t.dtstart AND
t2.dtstart <= t.dtstart AND
t2.id <> t.id
)
THEN 0 ELSE 1
END ) AS NoOverlapBefore
FROM tblstatus t JOIN
tblDevice d
ON t.device_id = d.id
)
SELECT group_id, SUM(total_seconds) as total_seconds
FROM (SELECT group_id, grp,
DATEDIFF(SECOND, MIN(dtstart), MAX(dtend)) AS total_seconds
FROM (SELECT t.*,
sum(t.NoOverlapBefore) over (partition BY group_id
ORDER BY t.dtstart, t.id) AS grp
FROM t
) t
GROUP BY grp, group_id
) t
GROUP BY group_id;
source to share
A bit collapsed, but I have a working solution. The trick was to change the presentation of the data.
EDIT: This solution works until there are two events on the same device at the same time.
I left the SQL script here: http://sqlfiddle.com/#!6/59e80/8/0
declare @tblGroup table (id int, name varchar(20))
insert into @tblGroup (id, name) values (1, 'Room 1'), (2, 'Room 2'), (3, 'Room 3'), (4, 'Room 4')
declare @tblDevice table (id int, name varchar(20), group_id int)
insert into @tblDevice (id, name, group_id) values (1, 'Computer', 1), (2, 'Camera', 1), (3, 'Computer', 2), (4, 'Switch', 2)
declare @tblStatus table (id int, device_id int, dtStart datetime, dtEnd datetime)
insert into @tblStatus (id, device_id, dtStart, dtEnd) values
(1, 1, '2015-05-12 01:40:00.0', '2015-05-12 01:40:20.0'),
(2, 2, '2015-05-12 01:40:01.0', '2015-05-12 01:40:27.0'),
(3, 3, '2015-05-12 03:43:03.0', '2015-05-12 03:46:14.0'),
(4, 4, '2015-05-12 03:43:00.0', '2015-05-12 03:46:12.0'),
(5, 2, '2015-05-12 07:12:10.0', '2015-05-12 07:12:22.0');
WITH eventlist as
(select
s.id,
s.device_id,
g.Id AS groupId,
g.name as groupName,
d.name as deviceName,
s.dtStart AS dt,
'GO_DOWN' AS eventtype,
1 AS eventcount
from
@tblStatus s
inner join
@tblDevice d on d.id = s.device_id
inner join
@tblGroup g on g.id = d.group_id
UNION
select
s.id,
s.device_id,
g.Id AS groupId,
g.name as groupName,
d.name as deviceName,
s.dtEND AS dt,
'BACK_UP' AS eventtype,
-1 AS eventcount
from
@tblStatus s
inner join
@tblDevice d on d.id = s.device_id
inner join
@tblGroup g on g.id = d.group_id
),
breakdown AS(
SELECT
principal.groupId
,principal.groupName
,principal.dt
,principal.deviceName
,principal.eventtype
,was_broken = ISNULL(SUM(before.eventcount),0)
,is_broken = ISNULL(SUM(before.eventcount),0) + principal.eventcount
FROM
eventlist principal
LEFT JOIN eventlist before ON before.groupId = principal.groupId
AND 1 = CASE WHEN before.dt < principal.dt THEN 1
WHEN before.dt = principal.dt AND before.device_id < principal.device_id THEN 1
ELSE 0 END
GROUP BY
principal.eventcount
,principal.deviceName
,principal.eventtype
,principal.groupId
,principal.groupName
,principal.dt
)
,breakdownstart AS
( SELECT groupId,dt, r = RANK() OVER (PARTITION BY groupId ORDER BY dt) FROM breakdown WHERE was_broken = 0 AND is_broken =1 )
,breakdownend AS
( SELECT groupId,dt, r = RANK() OVER (PARTITION BY groupId ORDER BY dt) FROM breakdown WHERE was_broken = 1 AND is_broken = 0 )
,breakgroup as
(SELECT s.groupId
,s.r
, break_start = s.dt
, break_end = e.dt FROM breakdownstart s INNER JOIN breakdownend e ON e.r = s.r AND e.groupId = s.groupId)
SELECT groupId,SUM(DATEDIFF(SECOND,break_start,break_end)) AS break_length FROM breakgroup GROUP BY breakgroup.groupId
source to share
Try the following:
select
g.id, SUM(DATEDIFF(SECOND, s.dtStart, s.dtEnd))
from
@tblStatus s
inner join @tblDevice d on d.id = s.device_id
inner join @tblGroup g on g.id = d.group_id
group by
g.id
You group the GroupId, then for every state you have in that group, you get the difference in seconds between the start time and the end time, and the SUM at the GroupId level.
source to share
I suggest grouping by id, the goal here is to get the difference between times, only then can you SUM.
SELECT
group.id, SUM(DATEDIFF(SECOND, status.dtStart, status.dtEnd))
FROM
@tblStatus status
inner join @tblDevice device ON device.id = status.device_id
inner join @tblGroup group ON group.id = device.group_id
GROUP BY
group.id
source to share