How to sum up the distinct Total Time of an Event ignoring duplicate overlaps in Times?

3 votes

2 answers

428 views

sql-server sql-server-2019 recursive time standard-edition

                          I have the following EventTimes table:

    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');

Notice:

 - The same Event can have two time ranges that overlap each other. E.g. same Event from 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 Event from 1 PM to 2 PM and also from 2:30 PM to 4 PM.

End Goal:

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 Event.)

The 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:



dbfiddle.uk for reference. 

---

Other Info:

 - If it's easier to work with datetimes, feel free to alter the data types from TIME to 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.

                        

Asked by J.D. (40893 rep)

Nov 17, 2023, 05:36 PM
Last activity: Nov 20, 2023, 10:05 PM

How to sum up the distinct Total Time of an Event ignoring duplicate overlaps in Times?

Related Questions