Hotel Reservation System (Available Room)

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 2008 Forums

Transact-SQL (2008)

Hotel Reservation System (Available Room)

Author

Topic

TheOxblood
Starting Member

19 Posts

Posted - 2011-09-20 : 10:03:50

I have a Hotel Reservation System, I am using SQLServer 2008 R2

I need to check on my ReservationTable if their will be an Available Room for reservation, my reservation table contains..

Reservation Table

PK - ReservationNo
FK - ReservationType
FK - ClientID
FK - RoomNo
- NoOfPersons
- NoOfHours
- ArrivalDate
- ArrivalTime
- HoursSpan
- ReservationDate

I should check it on the ArrivalDate and the ArrivalTime,
What sqlcommand should I use?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2011-09-20 : 10:07:22

please provide us with sample data and expected result based on that data. this way you'll get your answer much quicker.

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.9 out!

SQL Server MVP

TheOxblood
Starting Member

19 Posts

Posted - 2011-09-20 : 10:20:37

RoomNumber : 10
NumberOfHours : 6
Arrival Date : 09-20-11
Arrival Time : 10:00am

So, RoomNumber 10 will have the status of RESERVED only from the of 10:00am up to 4:00pm.

What if another reservation will come?
How can I validate that RoomNumber 10 is NOT AVAILABLE for the time of 09-20-11 10:00am - 4:00pm

Vonn Liquiran

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2011-09-20 : 10:25:35

Hello Vonn,

Can you provide the datatypes for your columns in reservation table (specifically arrival data and arrival time)?

Also, what about rooms which have no reservation (as of yet)? They do not appear to have a home in the reservation table. Are they stored in another table? How are they included in this check, or are they?

TIA.

TheOxblood
Starting Member

19 Posts

Posted - 2011-09-20 : 10:33:16

Reservation Table

PK - ReservationNo (bigint)
FK - ReservationType (int)
FK - ClientID (bigint)
FK - RoomNo (int)
- NoOfPersons (int)
- NoOfHours (int)
- ArrivalDate (smalldatetime)
- ArrivalTime (smalldatetime)
- HoursSpan (int)
- ReservationDate (smalldatetime)

The status of the Room which has no reservation is VACANT-READY, if the room was being reserved, the status will turn to RESERVED.

RoomTable
PK - RoomNo (int)
FK - RoomTypeID
FK - RoomStatusID

Vonn Liquiran

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2011-09-20 : 10:39:41

quote:
Originally posted by TheOxblood

Reservation Table

PK - ReservationNo (bigint)
FK - ReservationType (int)
FK - ClientID (bigint)
FK - RoomNo (int)
- NoOfPersons (int)
- NoOfHours (int)
- ArrivalDate (smalldatetime)
- ArrivalTime (smalldatetime)
- HoursSpan (int)
- ReservationDate (smalldatetime)

The status of the Room which has no reservation is VACANT-READY, if the room was being reserved, the status will turn to RESERVED.

RoomTable
PK - RoomNo (int)
FK - RoomTypeID
FK - RoomStatusID

Vonn Liquiran

I see, Thanks. One option would be to assemble the room and occupancy datetimespan. Then you can compare these results to the request reservation to determine if the datetime of the request falls within/out of the reserved status' of rooms, perhaps something like this for the inner results;


select  roomNumber,
	dateadd(mi, datepart(mi,arrivalTime), dateadd(hh,datepart(hh,arrivalTime),arrivalDate)) AS arrivalDateTime,
	dateadd(hh,hoursSpan,dateadd(mi, datepart(mi,arrivalTime), dateadd(hh,datepart(hh,arrivalTime),arrivalDate))) AS departureDateTime

from Reservation

HTH.

TheOxblood
Starting Member

19 Posts

Posted - 2011-09-20 : 10:46:19

quote:
Originally posted by ehorn

quote:
Originally posted by TheOxblood

Reservation Table

PK - ReservationNo (bigint)
FK - ReservationType (int)
FK - ClientID (bigint)
FK - RoomNo (int)
- NoOfPersons (int)
- NoOfHours (int)
- ArrivalDate (smalldatetime)
- ArrivalTime (smalldatetime)
- HoursSpan (int)
- ReservationDate (smalldatetime)

The status of the Room which has no reservation is VACANT-READY, if the room was being reserved, the status will turn to RESERVED.

RoomTable
PK - RoomNo (int)
FK - RoomTypeID
FK - RoomStatusID

Vonn Liquiran

I see, Thanks. One option would be to assemble the room and occupancy datetimespan. Then you can compare these results to the request reservation to determine if the datetime of the request falls within/out of the reserved status' of rooms, perhaps something like this for the inner results;

select
dateadd(mi, datepart(mi,arrivalTime), dateadd(hh,datepart(hh,arrivalTime),arrivalDate)) AS arrivalDateTime,
dateadd(hh,hoursSpan,dateadd(mi, datepart(mi,arrivalTime), dateadd(hh,datepart(hh,arrivalTime),arrivalDate))) AS departureDateTime,
from Reservation

HTH.

Thank You very much for that idea, I should check every part of the RESERVED ArrivalDate and Time and compare it to the REQUEST ArrivalDate and Time!

Am I correct? :)

Vonn Liquiran

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2011-09-20 : 10:50:03

quote:
Originally posted by TheOxblood

Thank You very much for that idea, I should check every part of the RESERVED ArrivalDate and Time and compare it to the REQUEST ArrivalDate and Time!

Am I correct? :)

Vonn Liquiran

yvw,

Yes, that is what I had in mind. Once you have reduced the arrival and departure to a common datetime datatype, you can perform a simple compare against the reservation request datetime.

HTH.

TheOxblood
Starting Member

19 Posts

Posted - 2011-09-20 : 10:55:44

ThankYouVeryMuch! :) This is for my SystemAnalysis&Design project :)

Vonn Liquiran

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources