Find overlapping appointments using SQL Server

Question

I have a table with appointments each having start_time and end_time. Is there an efficient way to find overlapping appointments using SQL Server?

Answer 1

Here is one method to find any appointment that overlaps with the next appointment:

select a.*
from (select a.*, lead(start_time) over (order by start_time, id) as next_start_time
      from appointments a
     ) a
where next_start_time < end_time;

Answer 2

It is a simple join:

DECLARE @t TABLE ( ID INT, ST TIME, ET TIME )

INSERT  INTO @t
VALUES  ( 1, '8:00', '9:00' ),
        ( 2, '9:00', '10:00' ),
        ( 3, '12:00', '13:00' ),
        ( 4, '12:30', '13:30' ),
        ( 5, '13:10', '13:20' ),
        ( 6, '14:00', '15:00' )


SELECT  t1.ID, t2.ID
FROM    @t t1
        JOIN @t t2 ON t1.ID < t2.ID 
                      AND (t1.ST > t2.ST
                      AND t1.ST < t2.ET
                      OR  t1.ET > t2.ST
                      AND t1.ET < t2.ET)

Output:

ID1 ID2
3   4
4   5

The idea here is to find lines when at least one edge of line is between the edges of other line:

   a--------------------b
c-------------d


   a--------------------b
              c-------------d


   a--------------------b
         c--------d

If [cd] overlaps [ab] at least c or d will be between a and b.