Available rooms for a time slot during a date range query...with day of week filter
-
Available rooms for a time slot during a date range query...with day of week filter
I have two tables,
rooms
CREATE TABLE [rooms] (
[room_id] [int] NOT NULL ,
[room_short] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[room_long] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [rooms_pk] PRIMARY KEY CLUSTERED
(
[room_id]
 
ON [PRIMARY]) ON [PRIMARY]
GO
sample data
room_id room_short room_long
----------- ---------------------------------------- --------------------------------------------------------------------------------
86 ASM FSSKICOLL Catskills Ski Mountain French Swiss Ski Lodge
20 BLIC 114 Brokaw Library and Information Center 114
91 BM 460 Best Music Center 460
413 CAP 108 Chemo/Astro/Psycho 108
498 CH 5 Coffee House 5
453 CW 8 Candy Wilson House 8
79 DH 105 Dilbert House 105
110 DL 237 Dough Library 237
106 ED 100C Egbert Duncan House 100C
68 EDA 212 Egbert Duncan Annex 212
69 EH B1 East House B1
113 GH AUD Green House Auditorium
124 HCC 11 John M. Holmes Consultation Center 11
143 HW 102 Howard Whey House 102
493 JET 387 Jason E. Thomas House 387
10 KB FLD Beer Brewer Stadium Field
164 KS 9 W. Korn Scorn House 9
198 LLA 223 Living Loving Academic Ctr 223
19 LLR 421 Living Loving Residential Ctr 421
530 MAB 129 Queen Mab Building 129
497 NH CR Netrebko House Conference Room
83 OFH WR Our Field Hall Weight Room
381 OLC 5 Oil Library Classroom 5
95 PRH 106 Psycho Research Hall Room 106
73 PSU WR Plammins Strength Union Weight Room
66 QC AER Quinn the Eskimo Aerobic Area
25 RH 4060 Rail House 4060
608 SRC 202 Strength Recreation Center Room 202
501 SRC TRK Strength Recreation Center Room 208/Track
315 VG 1A Victory Gymnasium 1A
75 VG NB Victory Gymnasium North Balcony
329 WA 103A Walker House 103A
330 WA 103B Walker House 103B
and sp_reservations
CREATE TABLE [sp_reservations] (
[rsrv_id] [int] NOT NULL ,
[room_id] [int] NOT NULL ,
[rsrv_start_dt] [datetime] NOT NULL ,
[rsrv_end_dt] [datetime] NOT NULL ,
CONSTRAINT [sp_reservations_pk] PRIMARY KEY CLUSTERED
(
[rsrv_id],
[room_id]
 
ON [PRIMARY]) ON [PRIMARY]
GO
sample data
rsrv_id room_id rsrv_start_dt rsrv_end_dt
----------- ----------- ------------------------------------------------------ ------------------------------------------------------
2343023 20 2007-02-01 09:30:00.000 2007-02-01 10:45:00.000
2352843 20 2007-02-01 11:00:00.000 2007-02-01 12:15:00.000
2287765 66 2007-02-01 08:00:00.000 2007-02-01 08:50:00.000
2288041 66 2007-02-01 09:00:00.000 2007-02-01 09:50:00.000
2288179 66 2007-02-01 10:00:00.000 2007-02-01 10:50:00.000
2289085 66 2007-02-01 10:00:00.000 2007-02-01 10:50:00.000
2288110 66 2007-02-01 11:00:00.000 2007-02-01 11:50:00.000
2289154 66 2007-02-01 11:00:00.000 2007-02-01 11:50:00.000
2287972 66 2007-02-01 11:00:00.000 2007-02-01 12:15:00.000
2289964 66 2007-02-01 11:00:00.000 2007-02-01 12:15:00.000
2287869 66 2007-02-01 12:00:00.000 2007-02-01 12:50:00.000
2289998 66 2007-02-01 12:30:00.000 2007-02-01 13:20:00.000
2290323 66 2007-02-01 13:00:00.000 2007-02-01 13:50:00.000
2259547 69 2007-02-01 09:30:00.000 2007-02-01 10:45:00.000
2256054 69 2007-02-01 14:00:00.000 2007-02-01 15:15:00.000
2266991 79 2007-02-01 09:30:00.000 2007-02-01 10:45:00.000
2266442 79 2007-02-01 11:00:00.000 2007-02-01 12:15:00.000
2266784 79 2007-02-01 14:00:00.000 2007-02-01 15:15:00.000
2267352 79 2007-02-01 17:30:00.000 2007-02-01 20:20:00.000
2293139 83 2007-02-01 09:30:00.000 2007-02-01 10:45:00.000
and I need to be able to return a list of available rooms for a
particular time slot within a date range. Also, within that date range, I want to be able to further refine the query
to allow users to limit it to one or more days of the week, and to be able to filter the list of rooms by
one or more building codes.
So I created a calendar table:
CREATE TABLE [calendar2] (
[day_date] [smalldatetime] NOT NULL ,
[day_of_week] [int] NOT NULL ,
CONSTRAINT [PK_calendar2] PRIMARY KEY CLUSTERED
(
[day_date]
 
ON [PRIMARY]) ON [PRIMARY]
GO
with smalldatetime values for every 15-minute time slot and the weekday (dw) datepart value that corresponds to
the day of the week for that time slot.
day_date day_of_week
------------------------------------------------------ -----------
2007-02-01 00:00:00 5
2007-02-01 00:15:00 5
2007-02-01 00:30:00 5
2007-02-01 00:45:00 5
2007-02-01 01:00:00 5
2007-02-01 01:15:00 5
2007-02-01 01:30:00 5
2007-02-01 01:45:00 5
2007-02-01 02:00:00 5
2007-02-01 02:15:00 5
2007-02-01 02:30:00 5
2007-02-01 02:45:00 5
2007-02-01 03:00:00 5
2007-02-01 03:15:00 5
2007-02-01 03:30:00 5
2007-02-01 03:45:00 5
2007-02-01 04:00:00 5
2007-02-01 04:15:00 5
2007-02-01 04:30:00 5
2007-02-01 04:45:00 5
here is my sql code so far
SELECT CAST((REPLACE(iq.room_short,SUBSTRING(iq.room_short,CHARINDEX(' ',iq.room_short)+1,10),'')) AS varchar(6))
as Building,
cast(SUBSTRING(iq.room_short,CHARINDEX(' ', iq.room_short)+1, 10) AS varchar(10)) as Room,
cast(iq.room_long as varchar(50)) as Description,
from
(select r.room_short, r.room_long
FROM rooms r
where r.room_id not in
(
--inner query
SELECT distinct r.room_id FROM calendar2 c
INNER JOIN sp_reservations sp
ON c.day_date BETWEEN sp.rsrv_start_dt AND sp.rsrv_end_dt
INNER JOIN rooms r ON sp.room_id = r.room_id
where c.day_of_week in (1,2) --weekday (dw) datepart value(s)
and DateAdd(d, DateDiff(d, 0, c.day_date), 0) <= '02/01/2007' --end of the date range
and DateAdd(d, DateDiff(d, 0, c.day_date), 0) >= '02/01/2007' --beginning of the date range
and convert(varchar(8), day_date, 108) <= convert(smalldatetime,convert(varchar(8),'06:15:00',8)) --end of the desired time slot
and convert(varchar(8), day_date, 108) >= convert(smalldatetime,convert(varchar(8),'06:00:00',8)) --beginning of the desired time slot
)
) iq
where CAST((REPLACE(iq.room_short,SUBSTRING(iq.room_short,CHARINDEX(' ',iq.room_short)+1,10),'')) AS varchar(6)) in ('CW','BM') --filter the list of rooms by
one or more building codes
It works ok for the most part, though I have found at least one flaw: if a user selects one or more days of the week
OUTSIDE the bounds of the date range, the inner query returns an empty set, causing the entire query to erroneously
indicate that ALL rooms are available.
Any ideas on something more accurate and efficient?
BTW, the users will eventually query the database via a web interface...
Thanks
-
Just thinking out loud. havent really taken this all the way to see if it works.
Chage the calendar file to only a date and maybe a day of week and maybe a workday/weekend/holiday flag if you care.
Add another file to have only the 15 minute time blocks.
maybe a view to cross join the date file and time file.
change the reservation table to reservation_id, roomid,datekey,timekey and have record for each 15 minute time block that the reservation is for. (yes the program that makes the reservations becomes more complicated. )
now to get whats available you can take the cross join view and do a where not exists against the reservation file
-
I don't know if the will help, but after looking at your design and query I gave it up as "not worth the effort". The design is not suited to what you want to do (at least in my opinion). So I figured rather than just criticizing I would offer an alternative design (or at least the start of one) that does solve your problem. I ended up investing much more time than I expected, because in the end I could not get the query for available room times to display the way I wanted. I could get it close, (I could get a list of all available start times), but I really wanted to see it in a friendlier manner. Maybe it wasn't needed but it was a fun challenge.
So whether or not this actually helps with your original question is debatable, but if you can change your design then you might consider something similar to the following:
(NOTE: I only did rudimentary testing on this code (a couple of date/times and 1 room), while the Trigger/Constraints seem to work I would normally want a much larger set of test data for something like this).
/*-----------------------------------------------------------------
File: DateOverlaps.sql
Date: 8 May 07
Auth: JLK
Desc: This file contains the code to create several objects (in an existing database) that can be used
to simulate a basic conference room reservations system, preventing duplicate or overlapping
reservations. The goal of the code is the last two queries that report on available reservation times
for a specific room.
-------------------------------------------------------------------------------------------------------------
Mods:
070508 - jlk - initial creation
-------------------------------------------------------------------------------------------------------------*/
--insure none of our objects exist
if exists (select 1 from sysobjects where id = object_id('reservations_') and type = 'U') drop table reservations_
go
if exists (select 1 from sysobjects where id = object_id('rooms_') and type = 'U') drop table rooms_
go
if exists (select 1 from sysobjects where id = object_id('buildings_') and type = 'U') drop table buildings_
go
if exists (select 1 from sysobjects where id = object_id('reservation_dates_') and type = 'U') drop table reservation_dates_
go
if exists (select 1 from sysobjects where id = object_id('reservation_times_') and type = 'U') drop table reservation_times_
go
if exists (select 1 from sysobjects where id = object_id('reservation_clock_') and type = 'U') drop table reservation_clock_
go
--Create all required DB objects
--NOTE: While not necessary for this simple design, normalizing the building/rooms into seperate
-- tables is usually the correct design (depends on how much additional information about the building
-- you would end up recording.
create table buildings_ (
id_ numeric(10,0) not null identity,
number_ char(5) not null,
name_ varchar(20),
constraint pk_buildings_ primary key (id_)
)
go
--insert test data
insert into buildings_ (number_,name_) values ('0100','Test 100')
insert into buildings_ (number_,name_) values ('0200','Test 200')
go
create table rooms_ (
id_ numeric(10,0) not null identity,
building_id_ numeric(10,0) not null,
number_ char(5) not null,
name_ varchar(20),
constraint pk_rooms_ primary key (id_),
constraint fk_rooms_01_ foreign key (building_id_) references buildings_ (id_)
)
go
--test data
insert into rooms_ (building_id_,number_,name_) values (1,'1001','Room 1001')
insert into rooms_ (building_id_,number_,name_) values (1,'1002','Room 1002')
insert into rooms_ (building_id_,number_,name_) values (2,'2001','Room 2001')
insert into rooms_ (building_id_,number_,name_) values (2,'2002','Room 2002')
go
--NOTE: Check statement on date_ insures no time is recorded in the datetime field
-- This is critical, otherwise duplicate dates could sneak in.
create table reservation_dates_ (
id_ numeric(10,0) not null identity,
date_ datetime not null constraint ckc_reservation_dates_01_ check (date_ = CAST(FLOOR( CAST( date_ AS FLOAT ) ) AS DATETIME)),
constraint pk_reservation_dates_ primary key (id_),
constraint ak_reservation_dates_ unique (date_)
)
go
--Create 10 Years worth of data
declare @counter1_ int
set @counter1_ = 0
while @counter1_ < 3650
begin
insert into reservation_dates_ (date_) values (dateadd(day,@counter1_,'1/1/2007'))
set @counter1_ = @Counter1_ + 1
end
go
--This table will record 24 hours of time data broken up by 15 minute intervals.
--NOTE: I don't actually enforce that the time must be 15 minute increments, though we could with some additional work.
create table reservation_times_ (
id_ numeric(10,0) not null identity,
time_ char(5) not null constraint ckc_reservation_times_01_ check ((time_ between '00:00' and '23:45') and (substring(time_,4,2) in ('00','15','30','45'))),
available_ char(1) not null default 'N' constraint cks_reservation_times_02_ check (available_ in ('Y','N')),
constraint pk_reservation_times_ primary key (id_),
constraint ak_reservation_times_ unique (time_)
)
go
--Create 24 hours worth of data
DECLARE @counter1_ smallint
DECLARE @counter2_ smallint
Declare @hh_ char(2)
declare @mm_ char(2)
declare @available_ char(1)
SET @counter1_ = 0
WHILE @counter1_ < 24
BEGIN
if @counter1_ < 10
set @hh_ = '0' + cast(@counter1_ as varchar)
else
set @hh_ = cast(@counter1_ as varchar)
--If not between the hours of 8am and 5pm then no rooms available
--NOTE: you can enforce any busness rule you want this way (no holidays, etc).
if @counter1_ > 7 and @counter1_ < 18
set @available_ = 'Y'
else
set @available_ = 'N'
SET @Counter2_ = 0
while @counter2_ < 60
begin
if @counter2_ < 10
set @mm_ = '0' + cast(@counter2_ as varchar)
else
set @mm_ = cast(@counter2_ as varchar)
insert into reservation_times_ (time_,available_) values (@hh_ +':'+@mm_,@available_)
set @counter2_ = @counter2_ + 15
end
SET @counter1_ = @counter1_ + 1
END
go
--Now Denormalize our Calendar Date/Time tables, in this case denormalization makes other parts of the application
--easier (try writting the reservation table trigger against the two date/time tables :-), increases performance without an unmanageable maintenance burden. (The result set for 10 years is 350,400 records
--Not something to be used in a drop down but that is easy to overcome by keeping the base tables.
--NOTE: While "Clock" is not the best description, I can't think of a better name (maybe reservation_date_clock_, but that is getting kind of long).
create table reservation_clock_ (
id_ numeric(10,0) not null identity,
date_ datetime not null constraint ckc_reservation_clock_01_ check (date_ = CAST(FLOOR( CAST( date_ AS FLOAT ) ) AS DATETIME)),
time_ char(5) not null constraint ckc_reservation_clock_02_ check ((time_ between '00:00' and '23:45') and (substring(time_,4,2) in ('00','15','30','45'))),
available_ char(1) not null default 'N' constraint cks_reservation_clock_03_ check (available_ in ('Y','N')),
constraint pk_reservation_clock_ primary key (id_),
constraint ak_reservation_clock_ unique (date_,time_)
)
go
--now move our data from the date/time tables by joining the original tables
--together to get a cartisian result set. Now we have all date/time combinations in
--a single place AND in order from lowest to highest (that is important if we are going to
--rely on the ID values in some of our calculations).
insert into reservation_clock_ (date_,time_, available_)
select date_, time_, available_
from reservation_dates_ rd, reservation_times_ rt
order by rd.date_, rt.time_
----------------------------------------------------------------------------------------------------------------------
--At this point we could drop the reservation_dates_ and reservation_times_ as they are really not needed any more.
----------------------------------------------------------------------------------------------------------------------
--NOTE: The next table does not prevent overlaping reservations. I.e we could add a reservations
-- for a room from 08:00 to 08:45 and a second reservation for the same room from 08:15 to 09:00
-- on the same date. To prevent that I will implement after insert/update trigger logic. The
-- best we can do here is prevent completely duplicate records or records that have the same
-- start or end times for the same room.
create table reservations_(
id_ numeric(10,0) not null identity,
room_id_ numeric(10,0) not null,
start_clock_id_ numeric(10,0) not null,
stop_clock_id_ numeric(10,0) not null,
constraint pk_reservations_ primary key (id_),
constraint fk_reservations_01_ foreign key (room_id_) references rooms_ (id_),
constraint fk_reservations_02_ foreign key (start_clock_id_) references reservation_clock_ (id_),
constraint fk_reservations_04_ foreign key (stop_clock_id_) references reservation_clock_ (id_),
constraint ckt_reservations_01_ check (stop_clock_id_ > start_clock_id_),
constraint ak_reservations_01_ unique (room_id_,start_clock_id_),
constraint ak_reservations_02_ unique (room_id_,stop_clock_id_)
)
go
--The following trigger implements the business logic to prevent the creation of overlapping
--reservation times, or reservations during "unavailable" times (Holidays, after work hours, etc)
Create trigger tr_reservations_IU_ on reservations_ for Insert,Update as
begin
if UPDATE(start_clock_id_) or UPDATE(stop_clock_id_)
begin
if exists(
--We have to check that start and stop values don't equal because "between" includes the end points
--and we could book a room reservation to begin on the same date/time that another reservation ends.
select 1
from reservations_ r join inserted i on (r.id_ <> i.id_)
where (r.room_id_ = i.room_id_)
and (r.start_clock_id_ <> i.stop_clock_id_)
and (r.stop_clock_id_ <> i.start_clock_id_)
and (
(r.start_clock_id_ between i.start_clock_id_ and i.stop_clock_id_) or
(r.stop_clock_id_ between i.start_clock_id_ and i.stop_clock_id_) or
(i.start_clock_id_ between r.start_clock_id_ and r.stop_clock_id_) or
(i.stop_clock_id_ between r.start_clock_id_ and r.stop_clock_id_)
)
 
begin
RAISERROR ('Overlapping Reservations Dates',16,1)
ROLLBACK
RETURN
end
if exists (select 1 from inserted i join reservation_clock_ rc on (rc.id_ between i.start_clock_id_ and i.stop_clock_id_)
where rc.available_ = 'N')
begin
RAISERROR ('Portions of the Reservation Date/Times fall within times marked as Unavailable',16,1)
ROLLBACK
RETURN
end
end
end
go
if exists (select 1 from sysobjects where id = object_id('GetAvailableTimes_') and type = 'TF') drop function GetAvailableTimes_
go
--User Defined Function to create a table of all available dates for a specific room.
create function dbo.GetAvailableTimes_(@roomId_ as numeric(10,0), @minDate_ as datetime, @maxDate_ as datetime)
returns @rt_ table (startId_ numeric(10,0), stopId_ numeric(10,0), date_ datetime, startTime_ char(5), stopTime_ char(5))
as
begin
declare @id_ numeric(10,0)
declare @lastid_ numeric(10,0)
declare @startid_ numeric(10,0)
declare @date_ datetime
declare @lastdate_ datetime
declare @startdate_ datetime
declare @time_ char(5)
declare @lasttime_ char(5)
declare @starttime_ char(5)
--I never could figure out a sql statement that would group on the break in sequential ids that would allow me to
--eliminate this cursor. If anyone out there can do it I would appreciate seeing the solution. In the mean time
--I'm simply going to populate the in-memory table with records representing contiguously available time. Any routine
--using the output of this function that wants the individual 15 minute increments back can simply select all from
--the reservation_clock_ where id between each of these records startid_/stopid_ values (i.e. a JOIN)
declare cur1_ cursor for
select id_, date_, time_
from reservation_clock_ rc
where (rc.date_ between @minDate_ and @maxDate_)
and id_ not in (select rc1.id_
from reservation_clock_ rc1 join
reservations_ r on (rc1.id_ > r.start_clock_id_ and rc1.id_ < r.stop_clock_id_)
where r.room_id_ = @roomId_)
and available_ = 'Y'
open cur1_
fetch next from cur1_ into @id_, @date_, @time_
select @lastid_ = @id_ - 1, @startid_ = @id_, @startdate_ = @date_, @starttime_ = @time_ --get our initial starts values set
while @@fetch_status = 0
begin
if @lastid_ + 1 < @id_
begin
--break in sequence so record the record in our return table
insert into @rt_ values (@startid_,@lastid_,@startdate_,@starttime_,@lasttime_)
select @startid_ = @id_, @startdate_ = @date_, @starttime_ = @time_ --reset for next group
end
select @lastid_ = @id_, @lastdate_ = @date_, @lasttime_ = @time_
fetch next from cur1_ into @id_, @date_, @time_
end
close cur1_
deallocate cur1_
if not exists(select 1 from @rt_ where startid_ = @startid_)
insert into @rt_ values (@startid_,@lastid_,@startdate_,@starttime_,@lasttime_)
return
end
go
--Create some valid reservations
insert into reservations_ (room_id_, start_clock_id_, stop_clock_id_) values (1,34,36)
insert into reservations_ (room_id_, start_clock_id_, stop_clock_id_) values (1,38,43)
insert into reservations_ (room_id_, start_clock_id_, stop_clock_id_) values (1,43,45) --This allowed, start matches previous end time
insert into reservations_ (room_id_, start_clock_id_, stop_clock_id_) values (1,45,46)
insert into reservations_ (room_id_, start_clock_id_, stop_clock_id_) values (1,48,53)
insert into reservations_ (room_id_, start_clock_id_, stop_clock_id_) values (1,54,55)
insert into reservations_ (room_id_, start_clock_id_, stop_clock_id_) values (1,58,63)
go
/* The following demonstrate the Constraints/Triggers validating invlaid reservations
--This will fail in trigger with overlap of rec #2
insert into reservations_ (room_id_, start_clock_id_, stop_clock_id_) values (1,37,39)
go
--This will fail in trigger. Both the begin and end times are available but it overlaps with #5 and #6
insert into reservations_ (room_id_, start_clock_id_, stop_clock_id_) values (1,47,57)
go
--this will fail in trigger, it is within the timespan of rec #7
insert into reservations_ (room_id_, start_clock_id_, stop_clock_id_) values (1,59,60)
go
--This will fail in trigger because part of the time is in an unavailable period
insert into reservations_ (room_id_, start_clock_id_, stop_clock_id_) values (1,31,33)
go
--This will fail in trigger because part of the time is in an unavailable period
insert into reservations_ (room_id_, start_clock_id_, stop_clock_id_) values (1,65,74)
go
--This should fail on a unique constraint, two records can't have same start time
insert into reservations_ (room_id_, start_clock_id_, stop_clock_id_) values (1,38,39)
go
*/
--This will retrieve all available times beteen the 1 and 3 of Jan 2007
--NOTE: It took quite a while to get this one to do what I wanted, but I think I have it now.
-- Basically I wanted to see continuous blocks of available time (start and stop) where
-- the start could overlap with the end of a reservation since if you reserved the room from 9:00 to 10:00
-- I should be able to reserve from 10:00 on, and the stop could equal the start of a reservation for the
-- same reason.
select *
from dbo.getavailabletimes_(1,'1/1/2007','1/3/2007')
--This will give you all available start times.
select rc.*
from reservation_clock_ rc join dbo.getavailabletimes_(1,'1/1/2007','1/3/2007') at on (rc.id_ >= at.startid_ and rc.id_ < at.stopid_)
-
jlk, i appreciate your efforts!
i'll take a look at your code, though i must say that i am stuck with an existing database design.
-
That seems to be the story of my life, stuck with someone elses mess. But I figure the scales probably balance as I don't maintain ALL of my old work!
Good luck, and sorry I wasn't more help, though working out the problem was fun.
James.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply