Which booking parties exceed their assigned room capacity?

Define the decision and input records

A time-conflict check cannot catch a single oversized party. Join each confirmed booking to its room and compare seats to capacity. Keep this per-party decision separate from concurrent room occupancy: summing all bookings over a whole day would mix non-overlapping sessions.

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 b.id,b.room_id,b.seats,r.capacity,b.seats-r.capacity AS excess_seats
FROM bookings b JOIN rooms r ON r.id=b.room_id
WHERE b.status='confirmed' AND b.seats>r.capacity ORDER BY b.id;

B4 reserves five seats in R2, whose capacity is four, so it exceeds capacity by one seat. B1 and B2 individually fit R1 even though they conflict in time. Confirm the party size and an appropriate room before changing the reservation.

Apply the result within its limits

Capacity here is a configured business value, not a claim about legal occupancy limits. Missing rooms are not returned by this inner join and require a separate referential-integrity check. Shared sessions need a concurrent seat-total calculation.

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.