find available rooms for a time slot during a date

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

find available rooms for a time slot during a date

Author

Topic

familysupportdba
Starting Member

2 Posts

Posted - 2007-05-04 : 15:40:20

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.

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

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

jshepler
Yak Posting Veteran

60 Posts

Posted - 2007-05-07 : 23:09:29

In other words, you want a list of rooms that do not have an entry in the reservations table for the specified timeslot range in the specified date range.

That suggests a NOT EXISTS.

How about something like


DECLARE @startDate datetime, @endDate datetime, @startTime datetime, @endTime datetime

SET @startDate = dateadd(d, datediff(d, 0, @passedStartDate), 0) -- strip time (sets to midnight)
SET @endDate   = dateadd(d, datediff(d, 0, @passedEndDate) + 1, 0) -- add 1 day to get midnight of next day
SET @startTime = dateadd(d, -datediff(d, 0, @passedStartTime), @passedStartTime) -- strip date (sets to min date)
SET @endTime   = dateadd(d, -datediff(d, 0, @passedEndTime), @passedEndTime)

SELECT  ...
FROM    Rooms r
WHERE   NOT EXISTS
        (
            SELECT  *
            FROM    sp_reservations
            WHERE   room_id = r.room_id
                    AND rsrv_start_dt >= @startDate
                    AND rsrv_end_dt < @endDate
                    AND dateadd(d, -datediff(d, 0, rsrv_start_dt), rsrv_start_dt) >= @startTime
                    AND dateadd(d, -datediff(d, 0, rsrv_end_dt), rsrv_end_dt) < @endTime
        )

/jeff

familysupportdba
Starting Member

2 Posts

Posted - 2007-05-09 : 07:58:59

thanks!

users also will want to specify a date range and say, check only the Fridays within the range for an available time slot (6am-6:15am, for example) any ideas on how to return an error if a user selects one or more days of the week OUTSIDE the bounds of the date range - say, if the range includes Monday through Tuesday and they choose a Friday? currently (in my code) if a user does that, the inner query returns an empty set, causing the entire query to erroneously indicate that ALL rooms are available.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources