Club overlapping time slots together in SQL Server

Question

I have a table which has records of overlapping time-slots.

E.g.: Calendar(Id int, StartDate datetime, EndDate datetime, EventTitle nvarchar(100), ...)

The records would be like

1. 01/20/2011 08:15 AM --- 01/20/2011 08:40 AM
2. 01/20/2011 08:20 AM --- 01/20/2011 08:55 AM
3. 01/20/2011 12:30 PM --- 01/20/2011 01:15 PM
4. 01/20/2011 02:00 PM --- 01/20/2011 02:45 PM
5. 01/20/2011 02:15 PM --- 01/20/2011 02:30 PM
6. 01/21/2011 12:30 PM --- 01/21/2011 01:15 PM
7. .......
8. .......

The table represents the event schedule of a person

I want to club the overlapping slots together to give:

1. 01/20/2011 08:15 AM --- 01/20/2011 08:55 AM
2. 01/20/2011 12:30 PM --- 01/20/2011 01:15 PM
3. 01/20/2011 02:00 PM --- 01/20/2011 02:45 PM
4. 01/21/2011 12:30 PM --- 01/21/2011 01:15 PM
5. ...
6. ...

I am stuck at this part of the problem... (I removed my code that I pasted here, as it looks mangled. I am using stackoverflow for the first time.)

Part of my actual requirement is to find a time slot [t] (let's say 25 minutes) between two given dates, which I can accommodate into the schedule. [t] has to be found between 8:00 AM and 05:00 PM on any day.

Answer 1

This is an interesting question.

For a given date, lets consider the overlaps (I entered all my date times as per yours but for the 1/1/2011):

SELECT        dt1.StartTime AS StartOverlap,
              CASE 
                WHEN dt1.EndTime > dt2.EndTime THEN dt1.EndTime
                ELSE dt2.EndTime
              END AS EndOverlap
FROM          datetimetest dt1, datetimetest dt2
WHERE         dt2.StartTime > dt1.StartTime
AND           dt2.StartTime < dt1.EndTime

This gives me:

StartOverlap        EndOverlap
01/01/2011 08:15:00 01/01/2011 08:55:00
01/01/2011 14:00:00 01/01/2011 14:45:00

Great, we now know the start and end times we can't use that overlap.

How do we get the other none overlapping times?

I would look at all the time periods that are in the overlapping ranges, and then select out the id's that are not in that date range:

SELECT id 
FROM (SELECT        dt1.StartTime AS StartOverlap,
                CASE 
                  WHEN dt1.EndTime > dt2.EndTime THEN dt1.EndTime
                  ELSE dt2.EndTime
                END AS EndOverlap
  FROM          datetimetest dt1, datetimetest dt2
  WHERE dt2.StartTime > dt1.StartTime
  AND dt2.StartTime < dt1.EndTime
 ) AS Overlaps, datetimetest dtt

WHERE dtt.StartTime >= Overlaps.StartOverlap 
AND dtt.EndTime <= Overlaps.EndOverlap

This gives me a list of all the ids that are in the overlaps. I then just select all of the entries that are NOT in the overlaps:

SELECT StartTime, EndTime FROM datetimetest
WHERE id NOT IN(
SELECT id 
FROM (SELECT        dt1.StartTime AS StartOverlap,
                CASE 
                  WHEN dt1.EndTime > dt2.EndTime THEN dt1.EndTime
                  ELSE dt2.EndTime
                END AS EndOverlap
  FROM          datetimetest dt1, datetimetest dt2
  WHERE dt2.StartTime > dt1.StartTime
  AND dt2.StartTime < dt1.EndTime
 ) AS Overlaps, datetimetest dtt

WHERE dtt.StartTime >= Overlaps.StartOverlap 
AND dtt.EndTime <= Overlaps.EndOverlap)

To give me:

StartTime           EndTime
01/01/2011 12:30:00 01/01/2011 13:15:00

I can then union both queries together to give me a list of all used time slots.

As we are effectively cross joining the same table, I would add an additional clause to each query to constrain the date to a single date.