Return to Revisions

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;

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 ROW_NUMBER() OVER (ORDER BY y.EventID, 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
)
   , j
AS
(
    SELECT z.EventID
         , z.stID
         , z.eID
         , z.id - RANK() OVER (ORDER BY z.EventID, z.grp, z.stID DESC) AS x
    FROM z
)
   , k
AS
(
    SELECT j.EventID
         , MIN(j.stID) AS stID
         , MAX(j.eID) AS eID
    FROM j
    WHERE j.x < 0
    GROUP BY j.EventID
           , j.x
    UNION ALL
    SELECT j.EventID
         , j.stID
         , j.eID
    FROM j
    WHERE j.x !< 0
)
SELECT EventID
     , CAST(SUM(DATEDIFF(mi, k.stID, k.eID)) AS DECIMAL(5, 1)) / 60 AS EventTime
FROM k
GROUP BY EventID;

fiddle for reference