Solved

Hotel availability chart driving me mad!

Posted on 2002-03-19
11
324 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
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Filter Question 8 86
ASP.net VB.net gather value of text box text change 4 54
Voice recognition ASP or ASP.NET or JavaScript 2 70
Insert Button on a table 16 38
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…
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

830 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