A Sql Challenge: Find Available Appointment Times
May 14, 2007 12:37 perm link Readers: 1861

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;

• Babu: May 15, 2007 05:31
  

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

  You might find this useful.

• Jeff: May 15, 2007 06: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: May 15, 2007 08: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: May 15, 2007 08: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: May 15, 2007 09: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: May 15, 2007 09: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: May 15, 2007 09:29
  

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

• codist: May 15, 2007 11: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: May 15, 2007 11:58
  

  So what do I win? :)

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

• codist: May 16, 2007 05: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.

• Add Comment