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; ``` | EventId | TotalTime | | -------:|---------:| | 1 | 11.5 | | 2 | 12.0 | **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 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 ) , 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; ``` | EventID | EventTime | | -------:|---------:| | 1 | 11.500000 | | 2 | 12.000000 | [DB Fiddle for reference](https://dbfiddle.uk/dn_ujvyE)