Reservations - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Reservations

Author

Topic

chikoshumba
Starting Member

6 Posts

Posted - 2007-09-08 : 14:05:07

Hi

I am very new to sql server and I would like to develop a hotel reservation system. This project has caused me untold grief over the past few days.

I have two tables, one that contains the room details. It contains all the rooms that can be found in the hotel.I also have a table where all the hotel sessions are recorded.

I have been able to come up with two different queries which some how should be able to work together to identify the rooms that are not in use on a particular date. The first query is shown below

SELECT roomNumber, sessionNumber
INTO [#temp]
FROM Session
WHERE (dateofStart BETWEEN '09 / 02 / 1999' AND DATEADD(day, - 1, '09 / 06 / 1999')) OR
(dateofEnd BETWEEN '09/02/1999' AND DATEADD(day, - 1, '09/06/1999')) OR
(dateofStart < '09/02/1999') AND (DATEADD(day, - 1, dateofEnd) > DATEADD(day, - 1, '09/06/1999'))

This query identifies all the rooms that are in use between
09 / 02 / 1999' and 09/06/1999.

The second query then searches for all the rooms that are not being used durring the specified period.

Second Query

SELECT Rooms.roomNumber
FROM Rooms LEFT OUTER JOIN
#Temp ON Rooms.roomNumber = Temp.roomNumber
WHERE (#Temp.roomNumber IS NULL)

How can these two queries be joined such that the output of the 1st query is then used in the second query. I would need to pick all the rooms that are availiable on that particular date.

If anyone could help me, I would be very greatful.

I have included the definitions for the tables below.

The definitions for the database are as follows

create database hotel
on primary
(
Name = 'hotel'
Filename = 'c:\Program Files\ Microsoft SQL Server\hotel.mdf
)
go

Use hotel
go

-- creates a session table --

create Table sessions
(
sessionNumber int Identity (1,1) Not Null Primary Key,
roomNumber varchar (10) Not Null References Room(roomNumber),
dateofStart smalldatetime Not Null,
dateofEnd smalldatetime Not Null,
)
GO

create Table Rooms
(
roomnumber varchar(10) Identity (1,1) Not Null Primary Key,
rate smallmoney,
)
Go

I have also included some sample data for the database

Sample data

roomNumber Rate

d1 $100
d2 $100
d3 $100
d4 $100
d5 $100
d6 $100
d7 $100
d8 $100
d9 $100

sessions

sessionNumber arrival departuredate roomNumber

1 9/6/1999 9/17/1999 d1
2 9/12/1999 9/17/1999 d5
3 9/2/1999 9/9/1999 d2
4 9/1/1999 9/6/1999 d3
5 9/12/1999 9/26/1999 d4

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2007-09-08 : 14:48:49

i guess when you don't have rows in session then the room is available?

select roomnumber
from rooms
except
select roomnumber
from sessions
where datearrival>=@searchdate and datedeparture<@searchdate

--------------------
keeping it simple...

chikoshumba
Starting Member

6 Posts

Posted - 2007-09-08 : 17:38:30

Hi

Thanks for the quick reply

I have tried that, but i have 1 major glitch . I am using sql server express edition until I can get my hands on a copy of server 2005. Unfortunately except is not recognised and its giving me some error message. Am I doing something wrong?

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2007-09-09 : 03:59:39

i haven't tried express... look up NOT EXISTS or NOT IN

--------------------
keeping it simple...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources