Ride Me Cycles' three workshop bays were booked from a shared calendar sheet, and it was entirely possible, and it happened, for two jobs to land in the same bay at overlapping times. One query is enough to catch that before the booking is saved.
BlackTor Group LtdDartmoor, UK
A note on this page
A reference, not a client write-up.
Illustrative, per the note on the main SQL page: not real client work. It sits alongside the Bill of Materials and Reorder Point pages as a deeper dive on one query pattern that comes up often enough to be worth its own write-up.
The issue
Checking a single start time isn't enough.
Each RepairBookings row records a bay, a bike registration and a start and end time. The check in place before this compared only start times: a new booking was rejected if another booking in the same bay started at exactly the same minute, and waved through otherwise.
That misses almost every real clash. A booking from 9:00 to 11:00 and a second one from 10:00 to 10:30 don't share a start time at all, but the second one is sitting squarely inside the first. The fix isn't a longer list of special cases, it's the correct general test for whether two time ranges overlap at all.
Interval overlap
BayBookingConflict: the correct test for two time ranges overlapping
-- BayBookingConflict: return every existing booking in the requested bay
-- that overlaps the proposed [?start_time, ?end_time) window, so the
-- booking screen can block the save and show exactly what it clashes with.
--
-- Two time ranges overlap unless one finishes at or before the other
-- starts. Negating that gives the overlap test directly, and it is
-- symmetric, so it catches every kind of clash: a new booking fully inside
-- an existing one, one that fully contains an existing one, and a partial
-- overlap on either edge -- not just a matching start time.
SELECT
booking_id,
bike_registration,
start_time,
end_time
FROM RepairBookings
WHERE bay_id = ? -- the bay being requested
AND start_time < ? -- existing.start_time < new.end_time
AND end_time > ? -- existing.end_time > new.start_time
ORDER BY start_time;
The three parameters bind, in order, to the requested bay_id, the new booking's end_time, and the new booking's start_time. Any row this returns is a genuine clash; an empty result means the bay is free for the whole window, and the booking can be saved.
This has to run inside the same transaction as the INSERT that follows it, the same principle covered in wrap multi-table changes in a transaction on the main SQL page: checking for a clash and then saving the booking as two separate, unprotected steps leaves a gap where two people booking at the same moment can both pass the check before either one saves.
For speed: an index on (bay_id, start_time) lets this run as an index range scan rather than a full table scan, worth having since the check runs on every single booking attempt, not just occasionally.
Result
A clash is caught before it's saved, not discovered on the day.
The booking screen now shows the conflicting job directly, at the point of booking, instead of two mechanics finding out they've both been given the same bay when one of them turns up.
