A Sql Challenge: Find Available Appointment TimesMay 13, 2007
I am working on a service appointment application, where a user can choose a service taking some multiple of 30 minutes, and be given a set of possible appointment times. It's easy to solve in code, but it seems an interesting challenge to create this in a single sql statement.
Since my readers are such an intelligent lot, I wonder if anyone has a solution to this.
The table has half hour slots, 48 per day. Times where the service is not available are marked as blocked, times with existing appointments have a foreign key to the appointment. I left out the irrelevant columns:
create table schedule ( id int auto_increment primary key, blocked boolean default true, halfhour time not null, day date not null, appointment_id int ); create view openslots as select * from schedule where not blocked and appointment_id is null;
The schedule table is preallocated by day and halfhour so the id's are consecutive. The requirement is to find all sets of consecutive slots for a given multiple of half hours. Note that appointments over a day boundary are not allowed.
The actual database is H2, basically assume SQL92 features.
Any ideas are welcome, I have some as well but need to move on instead of playing around with the sql; this has an easy code solution.
Changing the table definitions are also possible.