sql / expert
Snippet
Detecting Interval Overlaps with Self-Joins
Implements a non-equi self-join to detect scheduling conflicts. By comparing two sets of start and end dates, we identify rows that occupy the same resource at the same time.
snippet.sql
sql
1
2
3
4
5
6
SELECT a.booking_id, b.booking_idFROM room_bookings aJOIN room_bookings b ON a.room_id = b.room_idAND a.booking_id < b.booking_idAND a.start_date < b.end_dateAND a.end_date > b.start_date;
Breakdown
1
a.booking_id < b.booking_id
Prevents comparing a row with itself and ensures each pair is only reported once.
2
a.start_date < b.end_date AND a.end_date > b.start_date
Standard relational logic for identifying overlapping time periods.