By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
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 |