The Codist - Programmerthink

A Sql Challenge: Find Available Appointment Times

Posted: 05/14/2007, Perm Link Readers: 2722


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.

Tags: sql, database
Babu 05/15/2007 07:31

I've explained this in http://vsbabu.org/mt/archives/2004/04/30/whenishe_free.html - note that this is for Oracle

You might find this useful.

Jeff 05/15/2007 08:54

Here you go:

<a href="http://weblogs.sqlteam.com/jeffs/archive/2007/05/15/60207.aspx">http://weblogs.sqlteam.com/jeffs/archive/2007/05/15/60207.aspx</a>

Should work in any SQL dialect that supports correlated subqueries. I can modify it to remove that requirement as well, but derived tables must be supported. both are pretty basic SQL features (i think even MySQL supports these.)

Let me know what you think.

codist 05/15/2007 10:33

I tried the solution from Jeff, translated to H2

select day, min(halfhour) as StartTime, max(halfhour) as EndTime
from
  (select s.day, s.halfhour, s.blocked,
    (select count(*) from hair.schedule s2 
     where s2.day = s.day and 
           s2.halfhour <= s.halfhour and 
           s2.blocked != s.blocked
     ) as RunGroup
   from hair.schedule s
  ) x
where
  not x.blocked
group by
  day, RunGroup
having count(*) >= 3
order by day

But I only get 1 result per day (basically the whole block of time >= 3). Could be I made a mistake in the sql...

Jeff 05/15/2007 10:43

It is hard to guess without some sample data what might be happening. Your code looks fine to to me, but I am not familiar with H2.

Jeff 05/15/2007 11:17

This update will return multiple results instead of 1 big block. I think that this is more of what you are looking for. It is actually even shorter and maybe more efficient, I didn't check yet. Note that this code applies to my example and schema, you'll need to make minor tweaks to modify to yours. more on my blog in a few minutes.

declare @timeslotsNeeded int

set @timeSlotsNeeded = 3

select s.Day, s.TimeSlot, max(s2.TimeSlot) as EndTime

from schedule s

inner join schedule s2

on s2.day =s.day and

s2.TimeSlot between s.TimeSlot and s.TimeSlot @TimeSlotsNeeded- 1

where

s.Booked = 0 and s2.Booked = 0

group by s.Day, s.TimeSlot

having count(*) = @TimeSlotsNeeded

jeff 05/15/2007 11:29

Blog post has been updated. Note that in my previous comment, above, this line:

s2.TimeSlot between s.TimeSlot and s.TimeSlot @TimeSlotsNeeded- 1

should read:

s2.TimeSlot between s.TimeSlot and s.TimeSlot @TimeSlotsNeeded- 1

(there should be a PLUS ( ) before the variable ... it got edited out for some reason when posted)

jeff 05/15/2007 11:29

.. and it was edited out again! Well, see my blog for the code! :)

codist 05/15/2007 13:55

yeah that's a bug I need to fix. This code works

select s.day, s.slot, max(s2.slot) as EndTime
from hair.schedule s
inner join hair.schedule s2
   on s2.day =s.day and
      s2.slot between s.slot and s.slot PLUSSIGN 3 - 1
where
  not s.blocked and not s2.blocked
group by s.day, s.slot
having count(*) = 3
order by day,slot
Jeff 05/15/2007 13:58

So what do I win? :)

Actually, just a "thank you" would be kind of nice ....

codist 05/16/2007 07:45

Thank you for your help! The project itself has been shelved for a bit. It is a useful sql concept that wasn't covered well elsewhere.

Todd 07/27/2009 13:36

What if I need all the times that are free for a range. I want to make a web app that showes all the times that are free that a customer could book based on how my mins I pass the to the SQL. So I would want to see that they are 3 back to back times that have 15 mins of time