September 8, 2007 at 5:40 pm
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
September 9, 2007 at 12:20 pm
Probably needs some refining since you use dateadd
declare @datestart datetime /*start of period*/
declare @dateend datetime /*end of period*/
set @datestart='19990906'
set @dateend = '19990917'
select roomnumber,rate
from room /*check al rooms if in not use*/
where not exists /*not in use=no record found in sessions for that period*/
(select 1 from sessions where (dateofstart=@datestart)
and room.roomnumber= sessions.roomnumber)
September 10, 2007 at 7:18 am
Try this...
create Table #sessions
(
sessionNumber int Identity (1,1) Not Null,
roomNumber varchar (10) Not Null,
dateofStart datetime Not Null,
dateofEnd datetime Not Null,
)
create Table #Rooms
(
roomID int Identity (1,1) Not Null,
roomnumber varchar(10),
rate smallmoney,
)
--roomNumber Rate
insert into #rooms
select 'd1' ,100
union select 'd2' ,100
union select 'd3' ,100
union select 'd4' ,100
union select 'd5' ,100
union select 'd6' ,100
union select 'd7' ,100
union select 'd8' ,100
union select 'd9' ,100
--sessionNumber roomNumber arrival departuredate
insert into #sessions
select 'd1', '9/6/1999', '9/17/1999'
union select 'd5', '9/12/1999', '9/17/1999'
union select 'd2', '9/2/1999', '9/9/1999'
union select 'd3', '9/1/1999', '9/6/1999'
union select 'd4', '9/12/1999', '9/26/1999'
declare @AvailabilityCheckDate_Low datetime, @AvailabilityCheckDate_High datetime, @revDate datetime
set @AvailabilityCheckDate_Low = '09/02/1999'
set @AvailabilityCheckDate_High = '09/06/1999'
-- If passing in as variables, you should make sure they are in the right order.
if (@AvailabilityCheckDate_High < @AvailabilityCheckDate_Low)
begin
set @revDate = @AvailabilityCheckDate_Low
set @AvailabilityCheckDate_Low = @AvailabilityCheckDate_High
set @AvailabilityCheckDate_High = @revDate
end
select r.roomNumber, r.rate
from #rooms r
left join #sessions s on s.roomNumber = r.roomNumber
where @AvailabilityCheckDate_Low not between isnull(dateofStart,'12/31/2099') and isnull(dateofEnd,'12/31/2099') and
@AvailabilityCheckDate_High not between isnull(dateofStart,'12/31/2099') and isnull(dateofEnd,'12/31/2099') and
isnull(dateofStart,'12/31/2099') not between @AvailabilityCheckDate_Low and @AvailabilityCheckDate_High and
isnull(dateofEnd,'12/31/2099') not between @AvailabilityCheckDate_Low and @AvailabilityCheckDate_High
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply