Solved

Hotel availability chart driving me mad!

Posted on 2002-03-19
11
326 Views
Last Modified: 2012-05-04
I have developed a hotel availability chart.  The user selects their arrival and departure dates from drop down lists.  This queries the database to show any rooms that are available for the dates.

My database structure has the following tables:

Rooms
Bookings

My query currently looks like this:

SELECT *, 'images/pic-room0' & rooms.roomID & '.jpg' AS roompic from rooms WHERE roomID NOT IN(Select bookingroom  FROM bookings  WHERE bookingarrival >= #" & arrivalmonth & "/" & arrivalday & "/" & arrivalyear & "# AND bookingdeparture <= #" & departmonth & "/" & (departday) & "/" & departyear & "#)

However this shows rooms as available where the arrival date is prior to the date selected in the form.  I just can't get my head round this, any help would be much appreciated.

Thanks in advance

Robert Kennedy
0
Comment
Question by:Orroland
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
11 Comments
 
LVL 20

Expert Comment

by:Silvers5
ID: 6879638

try

SELECT *, 'images/pic-room0' & rooms.roomID & '.jpg' AS roompic from rooms WHERE roomID IN(Select
bookingroom  FROM bookings  WHERE bookingarrival >= #" & arrivalmonth & "/" & arrivalday & "/" & arrivalyear
& "# AND bookingdeparture <= #" & departmonth & "/" & (departday) & "/" & departyear & "#)
0
 

Author Comment

by:Orroland
ID: 6879843
This isn't working, let me explain a bit more:

The BOOKINGS tables is like this:

BOOKINGID
BOOKINGARRIVAL
BOOKINGDEPARTURE
BOOKINGROOM

So, I want to show rooms that are available between the arrival and departure date selected in drop down boxes.

Let's assume there is a booking for rooms 1 and 2 (5 rooms total) arriving on 03/20/2002 and departing on 03/22/2002.

The user wants the dates 03/21/2002 to 03/22/2002 so the query looks like this:

SELECT *WHERE roomID NOT IN(Select bookingroom  FROM bookings  WHERE bookingarrival >= 03/21/2002 AND bookingdeparture <= 03/22/2002

This will not work as the arrival date for the booking is 03/20/2002.  I am sure that I can use BETWEEN here somehow but I just can get my head round it.

Thanks again in advance.

Robert
0
 
LVL 8

Expert Comment

by:Barry62
ID: 6879901
could you add 1 to the arrival date prior to your qeury?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Accepted Solution

by:
ParthaChoudhury earned 150 total points
ID: 6880046
Robert,

The problem is with the design of the BOOKINGS table. You are storing arrival date and departure date, but when you want to show booking, you really want to compare with a matrix rather than a date range. You are trying to select rooms which are not booked for the same span of days, but your select cannot take care of partial overlaps?

Example:

Day->    1 2 3 4 5 6 7 8 9
Room  1  x x x x
      2    x x x x
      3      x x x x
      4              x x x
      5          x x x

If someone wants to book a room for days 3 through 6, the only option should be room #4. #3 is booked from days 3 through 6, so that is out. Though none of the rooms #1, #2 and #5 has arrival = 3 and departure = 6, but they cannot be booked because they have partial overlaps.

Solution:
The solution is to change the Bookins table structure to include one row per day of booking, since it will be a pain (if not almost impossible) to read the start and the end dates and build the matrix to compare with the dates of the new booking.

Booking
----------
Booking_Id     Integr  (PK)
Booking_Room   Integer (FK)
Booked_Dt      Date

Every time you somebody book a room, you'd create one or more rows with each day the room is booked for. And change your SQL to:

SELECT  *, 'images/pic-room0' & rooms.roomID & '.jpg' AS roompic
FROM    rooms
WHERE   roomID NOT IN(
     SELECT Booking_Room  
     FROM   Bookings  
     WHERE  Booking_Dt <>= #" & arrivalmonth & "/" & arrivalday & "/" & arrivalyear & "#
     AND    Booking_Dt <> #" & departmonth & "/" & (departday) & "/" & departyear & "#)


- Partha
0
 
LVL 1

Expert Comment

by:ParthaChoudhury
ID: 6880049
Oops, sorry the SQL should be

SELECT  *, 'images/pic-room0' & rooms.roomID & '.jpg' AS roompic
FROM    rooms
WHERE   roomID NOT IN(
     SELECT Booking_Room  
     FROM   Bookings  
     WHERE  Booking_Dt NOT BETWEEN #" & arrivalmonth & "/" & arrivalday & "/" & arrivalyear & "#
            AND #" & departmonth & "/" & (departday) & "/" & departyear & "#)

- Partha
0
 

Author Comment

by:Orroland
ID: 6880296
I would be willing to pay to fix this - I don't have the time or energy at the moment.

Could you please contact me at: mail@superwebs.co.uk if you are interested.

Thanks

Robert
0
 
LVL 1

Expert Comment

by:ParthaChoudhury
ID: 6880321
Sorry Robert - I don't have the time for that either, I'm too busy with my own full-time work. What I gave you looked like the solution to me. Please feel free to use the idea if you have anybody else to implement it for you.

Good luck

- Partha
0
 

Author Comment

by:Orroland
ID: 6880650
Thanks - just have to work it out myself.  The problem I am having is how to loop from the arrival date to the departure date and add a record for every room on that date so:

ARRIVAL DATE: 03/19/2002
DEPARTURE DATE: 03/21/2002
ROOMS SELECTED: 1,3

My Bookings table needs to record:

BOOKINGDATE: 03/19/2002
ROOM: 1

BOOKINGDATE: 03/19/2002
ROOM: 2

BOOKINGDATE: 03/20/2002
ROOM: 1

BOOKINGDATE: 03/20/2002
ROOM: 2

ETC..

How can I do this, at the moment all I do is add the arrival and departure dates.

Please help......
0
 

Author Comment

by:Orroland
ID: 6880655
Here is my first effort (for what its worth!?):

If Request("Submit") <> "" then
x = datediff("d", "arrivaldate", "departuredate")
For i = 1 to x
While Not rooms.EOF
insertbooking__roomID = rooms("roomID")
insertbooking__customerID = 99999
set insertbooking = Server.CreateObject("ADODB.Command")
insertbooking.ActiveConnection = MM_reiversrest_STRING
insertbooking.CommandText = "INSERT INTO bookings (bookingarrival, bookingroom, bookingcustomer)  VALUES (#" & Request("arrivalmonth") & "/" & (Request("arrivalday")-i) & "/" & Request("arrivalyear") & "#, " + Replace(insertbooking__roomID, "'", "''") + ", " + Replace(insertbooking__customerID, "'", "''") + " ) "
insertbooking.CommandType = 1
insertbooking.CommandTimeout = 0
insertbooking.Prepared = true
insertbooking.Execute()
rooms.MoveNext()
Wend
Next
End If
0
 
LVL 6

Expert Comment

by:ebosscher
ID: 6881002
you've got a bigger problem..

what happens if you have someone that booked from Jan 1 to Jan 10 and you're checking to see if the room is available between Jan 4 and Jan 6th?

the beginning and ending dates are out of the scope of the second query if you are just checking to see if someone elses start or end date is between the two dates selected on the form.

ParthaChoudhury was right.  You need a different data storage method.

I might suggest:

Hotel
Room
DatesOccupied

that way when someone books a range of dates you would fill inbetween the first date and the last date for the Dates occupied, and then you can select on not occupied between dateA and dateB

your first method assumes that the bookings will overlap the start and end date somehow, and thats not always going to be the case.

Hope you can work this out
0
 

Author Comment

by:Orroland
ID: 6882567
Alot of work to fix it but I am getting there - thanks
0

Featured Post

Get HTML5 Certified

Want to be a web developer? You'll need to know HTML. Prepare for HTML5 certification by enrolling in July's Course of the Month! It's free for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:   The Exchange of informatio…
I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

635 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question