SQL
Check for Overlapping Date and Time Ranges
Master the SQL logic to efficiently determine if two date or time ranges intersect, a critical skill for scheduling, booking, and resource management applications.
SELECT r1.id AS booking1_id, r2.id AS booking2_id
FROM Bookings r1
JOIN Bookings r2
ON r1.id < r2.id -- Prevents checking a range against itself and duplicates (r1, r2) vs (r2, r1)
AND r1.start_time <= r2.end_time
AND r1.end_time >= r2.start_time;
How it works: This query identifies all pairs of overlapping booking ranges within the Bookings table. It uses a self-join (Bookings r1 JOIN Bookings r2) and applies the condition r1.start_time <= r2.end_time AND r1.end_time >= r2.start_time to detect overlaps. The r1.id < r2.id condition ensures each pair is listed only once and avoids self-comparison.