Querying Based On Date Range - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Querying Based On Date Range

Author

Topic

westmich
Starting Member

35 Posts

Posted - 2007-04-05 : 19:11:26

I am building a simple reservation system, but I ran into an issue that isn't so simple. At least not to me.

I have a table of room types that includes a field for the number of units the hotel has. So, the hotel might have a room type called 'Standard' that has 12 units. So, 12 units are available minus what is already reserved. A second table of reservations has a field for the number of units reserved in a given date range. So if a user searches for availability on a given date range it should return the number of avail units in that date range.

For example: the standard room has 12 units. There is a reservation for 2 units from 4/15/2007 to 4/20/2007. If a user does a search for availability and their check-in and check-out dates overlap that reservation, the query should only return a result of 10 units.

I've tried several different quiries without the correct results. Rather then skew anyone else's thoughts on the SQL syntax, I'll just show the basic table structure.

RoomTypes (RoomTypeId, PropertyId, Title, NumberOfUnits)
Reservations (ReservationId, RoomTypeId, NumberOfUnits, CheckIn, CheckOut)

Westmich
Smart Web Solutions for Smart Clients
http://www.mindscapecreative.com

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-04-06 : 03:27:46

[code]
select a.RoomTypeID,
a.NumberofUnits,
a.NumberOfUnits - (select sum(b.NumberOfUnits)
from Reservations b
where b.RoomTypeID = a.RoomTypeID
and ('4/15/2007' between b.Checkin
and dateadd(d,-1,b.CheckOut))
) as RoomsAvailable

From RoomTypes a

[/code]
You would want to check by the individual day, not the range due to the value of vacant rooms would vary on each day of the stay. Just modify the above query to check each day of the range. This should show each roomtype and the amount of available rooms, let me know if this is what you wanted. I made it so someone can checkin on the same day someone is checking out. If this is not the case take out the dateadd statment so it does not adjust by -1.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources