The Codist

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.