How to check dates don't overlap in a table using TSQL.
- by Jon
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
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
