I have the following
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 );
With the following data:
-- 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');
- The same
Eventcan have two time ranges that overlap each other. E.g. same
Eventfrom 4 AM to 2 PM and and also from 6 AM to 11 AM.
- There can also be gaps between the two time ranges. E.g. same
Eventfrom 1 PM to 2 PM and also from 2:30 PM to 4 PM.
I'm trying to calculate the
TotalTime of a given
Event ignoring the duplicate overlapping time. E.g. for the set of ranges of 9 AM to 1 PM and 11 AM to 3 PM, the distinct
TotalTime should be 6 hours (9 AM to 3 PM). Conversely, I also don't want to count the time in the gaps between two time ranges. So for the set of ranges of 1 PM to 2 PM and 2:30 PM to 4 PM the
TotalTime should be 2.5 hours. (Note these are just subsets of the full example above, and the final result should be the sum of all of these unique times per
TotalTime should never exceed 24 hours, these times are all within a single day (as is the
TIME data type).
Expected final results for the provided examples in the scripts above:
If it's easier to work with datetimes, feel free to alter the data types from
DATETIME. I can convert the results back, no problem.
I believe some sort of recursion is needed to solve this. I feel I'm pretty close on a solution, but not quite there yet.
In a case such as 2:30 - 4:00 and 4:00 - 14:00, I'd expect the Total Time to be 11.5 hours.