Finding an available appointment slot

Got a bit of a tricky query (or group of queries if necessary) that I need to write. Thought I’d pop back to SPF for the first time in aaaages and see if the community can weigh in :slight_smile:

CREATE TABLE appointment_slots
, tutor_id  BIGINT       NOT NULL
, starts_at DATETIME     NOT NULL
, ends_at   DATETIME     NOT NULL

CREATE TABLE appointment_slot_bookings
, start_date DATETIME     NOT NULL
, end_date   DATETIME     NOT NULL
, booking_id BIGINT       NOT NULL

Every few days, each tutor logs in and specifies upcoming dates and times she is available for appointments.

(A constraint in the frontend code dictates that each appointment slot be 15 minutes long, and between 9am and 7pm. I guess this is largely irrelevant to the problem at hand though.)

Given a user_id (e.g. 3), and a required appointment length (e.g. 40 minutes), I want to find all available contiguous 40-minute windows that have neither been booked nor overlap any existing bookings, that fall within the next two weeks.

I don’t usually have many issues tackling SQL queries but have to admit this one has got me stumped before I’ve even been able to write a single line of code!

Any advice on where to begin would be greatly appreciated.

Please note table structure is still flexible at this point. If I should be designing my database differently, please say so!

Also, I realise the terminology and the nature of the problem suggest a homework assignment. Please be assured this is not the case!

Just realised my table logic is flawed slightly; slot_id in appointment_slot_bookings ought to read tutor_id!

i don’t think i can help (yet) because i don’t understand the 15-minute slots and why you are looking for 40-minute windows

perhaps you could upload some (meaningful) sample data for both tables? in sql dump format, so we can experiment on the queries ourselves

meanwhile, i like how you’ve anticipated scaling this system up, by being able to handle more than 2 billion tutors – but if that comes to pass, and if each tutor has more than one slot, you’re gonna need to upsize the auto_increments as well