• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 328
  • Last Modified:

Hotel availability chart driving me mad!

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
Orroland
Asked:
Orroland
1 Solution
 
Michel SakrCommented:

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
 
OrrolandAuthor Commented:
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
 
Barry62Commented:
could you add 1 to the arrival date prior to your qeury?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
ParthaChoudhuryCommented:
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
 
ParthaChoudhuryCommented:
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
 
OrrolandAuthor Commented:
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
 
ParthaChoudhuryCommented:
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
 
OrrolandAuthor Commented:
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
 
OrrolandAuthor Commented:
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
 
ebosscherCommented:
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
 
OrrolandAuthor Commented:
Alot of work to fix it but I am getting there - thanks
0

Featured Post

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!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now