Which confirmed room bookings overlap in time?

Define the decision and input records

Two intervals overlap when each starts before the other ends. Strict comparisons permit adjacent bookings to meet at a boundary. Restrict the pair to a.id less than b.id so each conflict appears once and a booking never conflicts with itself. The fixture stores comparable local times in one consistent ISO-like representation.

All records below are synthetic. Create these tables in a fresh SQLite database before running the read-only query. Quantities are units; monetary values are integer HKD cents, so divide by 100 when presenting HK dollars.

CREATE TABLE rooms(id TEXT PRIMARY KEY, capacity INTEGER);
CREATE TABLE bookings(id TEXT PRIMARY KEY, room_id TEXT, starts_at TEXT, ends_at TEXT, seats INTEGER, status TEXT);
CREATE TABLE waitlist(id TEXT PRIMARY KEY, booking_id TEXT, requested_seats INTEGER, joined_at TEXT);
INSERT INTO rooms VALUES ('R1',8),('R2',4);
INSERT INTO bookings VALUES ('B1','R1','2026-10-10 09:00','2026-10-10 10:00',5,'confirmed'),('B2','R1','2026-10-10 09:30','2026-10-10 10:30',4,'confirmed'),('B3','R1','2026-10-10 10:30','2026-10-10 11:30',2,'confirmed'),('B4','R2','2026-10-10 12:00','2026-10-10 13:00',5,'confirmed'),('B5','R2','2026-10-10 12:00','2026-10-10 13:00',1,'cancelled');
INSERT INTO waitlist VALUES ('W1','B3',7,'2026-10-08 09:00'),('W2','B3',2,'2026-10-08 10:00'),('W3','B3',1,'2026-10-08 11:00');

Run the query and compare the result

SELECT a.id AS first_booking,b.id AS second_booking,a.room_id
FROM bookings a JOIN bookings b ON a.room_id=b.room_id AND a.id<b.id
 AND a.starts_at<b.ends_at AND b.starts_at<a.ends_at
WHERE a.status='confirmed' AND b.status='confirmed'
ORDER BY a.id,b.id;

B1 and B2 overlap in R1. B2 ends at 10:30 and B3 starts at 10:30, so they are not flagged. B5 is cancelled and does not conflict with B4. Review the two confirmed bookings before choosing which one to reschedule; the query does not select a winner.

Apply the result within its limits

Room sharing is prohibited in this scenario. Add cleaning buffers to the effective intervals if required. Reject invalid or mixed-timezone timestamps before relying on text comparison; this is not a concurrency-safe booking constraint.

The supplied setup and result were executed with SQLite. Adapt the schema and status rules to a test copy of your system, then agree the expected result with the process owner. These examples have not been tested on other database platforms.