(Note: Updated 5/15/2007 @ 12:25 PM EST to show another possible solution with different results.)
Here's my response to the
SQL Challenge given here. The challenge involves having a schedule table with days and times, and displaying all available consecutive free time slots to schedule an event.
This can be tough to solve in SQL, but it can be done. One way is using the "run/streak detection" technique shown
here (my very first SQLTeam article from quite a few years back), and another is basically using my favorite join - a CROSS JOIN! Both solutions are given below.
Note that I changed his schema a little; the table has a natural primary key of Day/TimeSlot, and TimeSlot is just an integer so that I could create simple sample data with only 8 "TimeSlots" per day.
create table schedule
(
[day] datetime not null,
TimeSlot int not null,
Booked bit not null,
primary key ([day],TimeSlot)
)
-- add some sample data
insert into schedule
select '1/1/2007',1,0 union all
select '1/1/2007',2,0 union all
select '1/1/2007',3,0 union all
select '1/1/2007',4,1 union all
select '1/1/2007',5,0 union all
select '1/1/2007',6,1 union all
select '1/1/2007',7,0 union all
select '1/1/2007',8,0 union all
select '1/2/2007',1,0 union all
select '1/2/2007',2,0 union all
select '1/2/2007',3,0 union all
select '1/2/2007',4,0 union all
select '1/2/2007',5,1 union all
select '1/2/2007',6,0 union all
select '1/2/2007',7,0 union all
select '1/2/2007',8,0 union all
select '1/3/2007',1,0 union all
select '1/3/2007',2,1 union all
select '1/3/2007',3,1 union all
select '1/3/2007',4,0 union all
select '1/3/2007',5,0 union all
select '1/3/2007',6,0 union all
select '1/3/2007',7,1 union all
select '1/3/2007',8,0
Solution #1
With our sample data in place, here's one fairly simple solution (only 1 SQL statement) using the run/streak method that returns available time ranges that will accommodate blocks the size of the @TimeSlotsNeeded variable:
declare @timeslotsNeeded int
set @timeSlotsNeeded = 3
select [day], min(TimeSlot) as StartTime, max(TimeSlot) as EndTime
from
(select s.[day], s.TimeSlot, s.Booked,
(select count(*) from schedule s2
where s2.[day] = s.[day] and
s2.TimeSlot <= s.TimeSlot and
s2.Booked != s.Booked
) as RunGroup
from schedule s
) x
where
x.Booked = 0
group by
[day], RunGroup
having count(*) >= @TimeSlotsNeeded
day StartTime EndTime
----------------------- ----------- -----------
2007-01-01 00:00:00.000 1 3
2007-01-02 00:00:00.000 1 4
2007-01-02 00:00:00.000 6 8
2007-01-03 00:00:00.000 4 6
(4 row(s) affected)
Note that, as mentioned in the comments, this solution will return basically only 1 row for the entire day if the entire day is open. If you really want to return all possible blocks of time that are open, then see the next solution.
Solution #2
Here, we use basically a CROSS JOIN (in this case, actually an INNER JOIN that has a cross-join effect) to join the schedule table to itself, joining each time slot per day to the next @TimeSlotsNeeded after it that are open. If the join results in @TimeSlotsNeeded matches, we have that entire block open. We can determine this by GROUPing on TimeSlot and ensuring that the COUNT(*) equals the @TimeSlotsNeeded, as shown:
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
Day TimeSlot EndTime
----------------------- ----------- -----------
2007-01-01 00:00:00.000 1 3
2007-01-02 00:00:00.000 1 3
2007-01-02 00:00:00.000 2 4
2007-01-02 00:00:00.000 6 8
2007-01-03 00:00:00.000 4 6
(5 row(s) affected)