SQL select query for available rooms does not work when each room is booked more than once

Question

I am currently making a booking system for a hotel. My select query for available rooms works perfect when a rooms been booked only once. The statement is meant to show a room in a table when it is available and to show nothing if they're not available on the selected date. This doesn't work when a room is booked twice even when the selected date is different to when it was booked. It still shows the room even though it is unavailable.

Here is the query

SELECT DISTINCT 
    Room_Type.Room_typeID, Room_Type.Room_tariffID, Room_Type.Description
FROM            
    Room_Type 
INNER JOIN
    Booking ON Booking.Room_typeID = Room_Type.Room_typeID
WHERE        
    (Room_Type.Description = @Param1) 
    AND (Booking.Check_in_date <> @Param2)

Booking table

BookingID
CustomerID
Room_TypeID
Check_In_Date
Check_Out_Date
Num_Of_Guests

Room_type table

Room_TypeID
Room_TarriffID
Description

Code to call the query

Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
Dim SearchDB As New HotelTableAdapters.Room_TypeTableAdapter
Dim GetAvailaibility As Hotel.Room_TypeDataTable = SearchDB.GetDataByRoomAvailability(DDRoomType.Text, Calendar1.SelectedDate.ToShortDateString())

If GetAvailability.Rows.Count > 0 then

Dim RoomDescription As String = GetAvailaibility(0)("Description")
Dim Price As Integer = GetAvailaibility(0)("Room_TariffID")
Dim Roomtypeid As Integer = GetAvailaibility(0)("Room_TypeID")
Dim DateCheckin As Date = Calendar1.SelectedDate.ToShortDateString

IDlabel.Text = Roomtypeid
roomlabel.Text = RoomDescription
pricelabel.Text = Price
Session("Room_TypeID") = Roomtypeid
Session("Description") = RoomDescription
Session("Check_in_date") = DateCheckin
Session("Room_TarriffID") = Price

Else Label1.text = ("Room Unavailable")

Your question is incomplete. (1) sample data; (2) desired results; (3) tag for the database you are using. — Gordon Linoff, Apr 22 '17 at 18:46
Without seeing more about your data structure we'd be mostly guessing. — Steve Lovell, Apr 22 '17 at 18:57
If that's the data structure, how do you know which rooms the bookings are in? — Steve Lovell, Apr 22 '17 at 19:56
Re-reading, I think you missed `Room_TypeID` from your list of fields in the `booking` table. — Steve Lovell, Apr 22 '17 at 20:05

Steve Lovell · Answer 1 · 2017-04-23T08:59:26.207

0

Regardless of the data structure, your current query is effectively: (a) find the room bookings which are not at the requested time. You want: (b) find the rooms which don't have bookings at the requested time.

The following may be approximately correct:

SELECT
    Room_Type.Room_typeID, Room_Type.Room_tariffID, Room_Type.Description
FROM            
    Room_Type 
WHERE
    Room_Type.Description = @Param1
    AND NOT EXISTS (
                    SELECT 1 FROM Booking
                    WHERE Room_Type.Room_typeID = Booking.Room_typeID
                    AND @Param2 = Check_In_Date
                    )

This feels wrong to me, though. Here's why:

It won't be what you want if there are multiple rooms of any type.
We aren't handling check-out-dates of the person making the request or of bookings already in the system.

If you want to start handling check-out-dates, you'll want approximately this logic in the not exists:

WHERE
Room_Type.Room_typeID = Booking.Room_typeID
AND @ParamCheckIn < Check_Out_Date
AND @ParamCheckOut > Check_In_Date

The < and > operators assume that when someone is staying 1 night, the Check_Out_Date = Check_Out_Date + 1, so that someone can check-in on the same date as another person checks-out.

edited Apr 23 '17 at 08:59

answered Apr 22 '17 at 20:17

Steve Lovell

2,564
2
13
16

Thanks for the response! We are not handling the check out date at the moment as I think its too difficult. So we are concentrating on customers booking for one day only. Once that works I think we will try and have a go at check-out-date, although our deadline is Monday morning. – Alex Hall Apr 22 '17 at 20:41
So I should try AND @Param2 <> Check_In_Date ? – Alex Hall Apr 22 '17 at 20:42
It's a `NOT Exists`, so it's `@Param2 = Check_In_Date` – Steve Lovell Apr 22 '17 at 21:22
I've edited my answer accordingly. If it meets your needs, please mark the answer as accepted by way of a thank-you and to help other users find useful responses. – Steve Lovell Apr 23 '17 at 09:00
when using this query I keep getting this error. 'Conversion failed when converting date and/or time from character string'. – Alex Hall Apr 23 '17 at 09:59
I'd guess your @Param2 is a string and not a date. You could either convert this to a date in your application code, or in the SQL. I'm no expert on the former, for the latter it depends on how your string is formatted. You'll want to do something like `convert(date,@Param2,103)` where 103 is the format type. There is a helpful document here: https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql – Steve Lovell Apr 23 '17 at 10:05
Struggling here. Its strange because when the INNER JOIN is in the query then @Param2 is a date. But with INNER JOIN I have the original problem. – Alex Hall Apr 23 '17 at 10:39
It could be the other way around. Is the field in the database definitely a date? Also depending on what version of sql-server (I assume that's what you're using, you didn't say), you might need `datetime` rather than `date`. – Steve Lovell Apr 23 '17 at 10:43
Definitely a date yes. Im using Visual studio 2013 with SQL Server. I will try datetime – Alex Hall Apr 23 '17 at 10:46
When trying to convert an error message pops up saying 'Must declare scalar variable "@Param1". Must declare scalar variable "@Param2". – Alex Hall Apr 23 '17 at 10:48
Double check the code works when you hard-code some values. If so, at this point it has to do with the wrapper and not the query. I only rarely use .NET, and tend to use Store Procedures to connect to the DB. So I'm no expert here. Did you have it returning data previously? – Steve Lovell Apr 23 '17 at 10:53
When I execute the query on the query builder, it works fine. On the code to call the query it says 'Public override Function Getdatabyroomavailability(Param1 as string, Param2 as string) As hotel.Room_typeDataTable'. I presume I want Param2 as date? – Alex Hall Apr 23 '17 at 11:01
Not sure, if you do that you should then be able to remove the `convert` though. The accepted answer to the following is more like I'm used to doing it: http://stackoverflow.com/questions/11139791/how-to-use-parameters-in-an-sql-command-in-vb – Steve Lovell Apr 23 '17 at 11:03
Thats way out of my depth at the moment. I think I will have to stick with my original query and just book a room once in the demonstration. – Alex Hall Apr 23 '17 at 11:12
One last idea, as unless something else has changed, I have no other idea why your original query would function (albiet incorrectly) while this query wouldn't. Can you try changing the `@Param2 = Check_In_Date` for `Check_In_Date = @Param2`. Your original also had some brackets which I'd thought were unnecessary. It might be worth trying it with them (and similar) re-introduced. Sorry not to be more help. – Steve Lovell Apr 23 '17 at 11:17
the query just changes it back to @Param2 = Check_in_date automatically. I think the only reason why the original query functions is because of the INNER JOIN to the booking table. Thanks for taking the time out to help, its much appreciated. – Alex Hall Apr 23 '17 at 11:26

SQL select query for available rooms does not work when each room is booked more than once

1 Answers1