How to check dates don't overlap in a table using TSQL

Question

I have a table with start and finish datetimes that I need to determine if any overlap and not quite sure the best way to go.

Initially I was thinking of using a nested cursor as shown below which does work, however I'm checking the same records against each other twice and I'm sure it is not very efficient.

eg: this table would result in an overlap.

id  start                       end
-------------------------------------------------------
1   2009-10-22 10:19:00.000     2009-10-22 11:40:00.000
2   2009-10-22 10:31:00.000     2009-10-22 13:34:00.000
3   2009-10-22 16:31:00.000     2009-10-22 17:34:00.000

Declare @Start datetime, @End datetime, @OtherStart datetime, @OtherEnd datetime, @id int, @endCheck bit

Set @endCheck = 0

DECLARE Cur1 CURSOR FOR
        select id, start, end from table1 

OPEN Cur1
FETCH NEXT FROM Cur1 INTO @id, @Start, @End
WHILE @@FETCH_STATUS = 0 AND @endCheck = 0
BEGIN
    -- Get a cursor on all the other records
    DECLARE Cur2 CURSOR FOR
            select start, end from table1 
                and id != @id AND start < @end
    OPEN Cur2
    FETCH NEXT FROM Cur2 INTO @OtherStart, @OtherEnd
    WHILE @@FETCH_STATUS = 0 AND @endCheck = 0
    BEGIN

            if ( @Start > @OtherStart AND @Start < @OtherEnd    OR
                 @End > @OtherStart AND @End < @OtherEnd )
                or
               ( @OtherStart > @Start AND @OtherStart < @End    OR
                 @OtherEnd > @Start AND @OtherEnd < @End )

            BEGIN
                SET @endCheck = 1
            END

            FETCH NEXT FROM Cur2 INTO @OtherStart, @OtherEnd
    END
    CLOSE Cur2
    DEALLOCATE Cur2

    FETCH NEXT FROM Cur1 INTO @id, @Start, @End
END
CLOSE Cur1
DEALLOCATE Cur1

Answer 1

• First, let's exhaustively examine what an "overlap" means and optimize your boolean expression.

---

An overlap is any of the following conditions:

                 Start1                     End1
---------------------------------------------------------------------------------
 Start2                       End2
 Start2                                                       End2
                             Start2                           End2

And is NOT any of the following conditions:

                 Start1                     End1
---------------------------------------------------------------------------------
 Start2  End2
                                                      Start2  End2

If you look carefully, you'll notice Start2 < End1 AND End2 > Start1 defines this relationship, so you can reduce your expression to this.

---

• Second, you can put this in a WHERE condition for your cursor instead of looping through every row and checking.

• Third, since you're just checking whether something exists or not, you can use an IF EXISTS clause instead of looping through a cursor and checking.

• Finally, you can condense this all down to a single query using an INNER JOIN on itself or WHERE EXISTS, depending on how you want your final output to look. To get all the overlaps in the table, you can try something like the following (the t2.id > t1.id is to only get each overlap once):

  SELECT t1.id, t2.id
  FROM MyTable t1
  INNER JOIN MyTable t2 ON t2.id > t1.id 
                        AND t2.start < t1.end AND t2.end > t1.start
  

Answer 2

I'm not certain what you'd like the resulting output to look like, but you can try this:

SELECT 
    TD1.* 
FROM 
    table1 TD1 
    JOIN table1 TD2 ON 
        TD1.start BETWEEN TD2.start AND TD2.[end] 
        AND TD1.id != TD2.id