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
Code MySQL:CREATE TABLE appointment_slots ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT , tutor_id BIGINT NOT NULL , starts_at DATETIME NOT NULL , ends_at DATETIME NOT NULL ); CREATE TABLE appointment_slot_bookings ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT , slot_id INT UNSIGNED NOT NULL , 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!