Unioning overlapping time ranges, then summing the total time

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

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;