Return to Revisions

Here is a solution that uses Run Length Encoding to compress overlapping time ranges -

SCHEMA AND DESIRED OUTPUT

DROP TABLE IF EXISTS dbo.EventTimes;
CREATE TABLE dbo.EventTimes
(
  EventTimeKeyId INT IDENTITY(1,1) PRIMARY KEY,
  EventId INT NOT NULL,
  StartTime TIME NOT NULL,
  EndTime TIME NOT NULL
);

-- Event 1
INSERT INTO dbo.EventTimes (EventId, StartTime, EndTime)
VALUES 
  (1, '04:00:00', '14:00:00'), 
  (1, '06:00:00', '11:00:00'), 
  (1, '09:00:00', '12:00:00'), 
  (1, '13:00:00', '14:00:00'), -- Gap between this row and the next row
  (1, '02:30:00', '04:00:00'); -- Notice the half-hour on this one

-- Event 2
INSERT INTO dbo.EventTimes (EventId, StartTime, EndTime)
VALUES 
  (2, '00:00:00', '06:00:00'), -- Gap between this row and the next row
  (2, '09:00:00', '13:00:00'), 
  (2, '11:00:00', '15:00:00');

-- Final Results expected for these 2 examples
SELECT 1 AS EventId, 11.5 AS TotalTime
UNION ALL
SELECT 2 AS EventId, 12 AS TotalTime;

SOLUTION

WITH x
AS
(
    SELECT EventID
         , MAX(EndTime) AS eID
         , StartTime
    FROM EventTimes
    GROUP BY EventID
           , StartTime
)
   , y
AS
(
    SELECT EventID
         , MIN(x.StartTime) AS stID
         , x.eID
    FROM x
    GROUP BY x.EventID
           , x.eID
)
   , z
AS
(
    SELECT y.stID DESC) AS id
         , y.EventID
         , y.stID
         , y.eID
         , IIF((
                   LAG(y.stID) OVER (ORDER BY y.EventID, y.stID DESC) <= y.eID
                   AND LAG(y.EventID) OVER (ORDER BY y.EventID, y.stID DESC) = y.EventID
               )
               OR
               (
                   LEAD(y.eID) OVER (ORDER BY y.EventID, y.stID DESC) >= y.stID
                   AND LEAD(y.EventID) OVER (ORDER BY y.EventID, y.stID DESC) = y.EventID
               )
             , 1
             , 0) AS grp
    FROM y
)
   , k
AS
(
    SELECT z.EventID
         , MIN(z.stID) AS stID
         , MAX(z.eID) AS eID
    FROM z
    WHERE z.grp = 1
    GROUP BY z.EventID
           , z.grp
    UNION ALL
    SELECT z.EventID
         , z.stID
         , z.eID
    FROM z
    WHERE z.grp = 0
)
SELECT EventID
     , CAST(SUM(DATEDIFF(mi, k.stID, k.eID)) AS DECIMAL(5, 1)) / 60 AS EventTime
FROM k
GROUP BY EventID;

DB Fiddle for reference

This particular solution treats time ranges that edge each other as non-overlapping, though argument could be made that they are. (e.g. 2:30 - 4:00 and 4:00 - 14:00).