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 :)
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!