Unioning overlapping time ranges, then summing the total time

Question

I have a hardware group and many devices into this group. Example:

+ Room 1
|-- Computer
|-- Camera
+ Room 2
|-- Computer
|-- Switch

All devices are monitored using ping. When some device is not working the program add a row into a table saying the start of break. When the device back on then the program update this row saying the end of break.

It's ok to know the total break seconds for each device. My need is know the real sum time of all 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 real down time of group "Room 1" is 39 seconds (NOT 58).

01:40:00 - 01:40:20 = 20 seconds
01:40:01 - 01:40:27 = 26 seconds
07:12:10 - 07:12:22 = 12 seconds

About the two first lines, take a look why is 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 per group. How can I do it using SQL?

To help on 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

Answer 1

You are looking to combine the different groups into "islands" and to count the extend of the islands. That is why this type of problem is sometimes called gaps and islands.

Let me assume that you are using SQL Server 2012+. That slightly simplifies the calculations. The idea is to determine the starts and ends for overlapping groups. The following determines if a group has 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

With this, you can just assign to each row in the table the number of "NoOverlapBefore" records that occur before it and use 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. The SQL Fiddle is a big help. Here is one that actually works.

The query is:

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;

Answer 2

A bit convoluted, but I have a working solution. The trick was to alter the data presentation.

EDIT : This solution works as long as there are never two events that take place on the same device at the same time.

I left a SQL Fiddle 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

Answer 3

I suggest grouping by id, the goal here is to get the difference between the times, only then you can 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

Answer 4

Try this:

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 by GroupId, then for each status you have on that group you get the difference in seconds between the start time and the end time, and SUM it aggregated to the GroupId level.