Checking available room resevation between check-in and check-out date with SQL in ASP.net

Question

I could use some advice in how to create my select statement, so it will work with the check-in and check-out dates. I have 2 tables: Rooms and Booking. To help you understand, they look like this:

Rooms table:
Room number (prim-key)
Type
Price

Booking table:
BookingId (prim-key)
Check-In date:
Check-Out date:
Room-number (foreign key) to Room number in Room table.

I have an Check available room button which run the select statement in my gridview. Based on input from user it should find the rooms that are not reserved already.

The datatype of Check-in and Check-out are "Date" DD/MM/YYY" Lets say if room 101 is booked between the 13-07-2015 and the 15-07-2015. The table will then looks like this.

BookingId = a long number  ,   Check-In = 13-07-2015  ,   Check-Out = 15-07-2015  ,  RoomNumber = 101.

So , how do i make the select statement if a user writes in the dates: 14-07-2015 too 16-07-2015? Then it should not show room 101, because it's reserved.

Hope someone can help guide me in the right direction. If you need any code or something , please let me know!

Update:

I'm still trying to make this work.. not sure what causing the issues i got. Right now when i run the code from Tim and Hogan (tried them both) it will retrieve all the room in the hotel, and not sort the specific rooms out which is reserved.

As you see at the picture below, room 102 is reserved

Code of text boxes where users write in the dates:

 <div class="form-group">
    <asp:Label ID="CheckinLabel" runat="server" Text="Check-in Date"></asp:Label>
    <asp:TextBox ID="datetimepicker1" ClientIDMode="Static" runat="server" CSSclass="form-control"></asp:TextBox>
 </div>
 <div class="form-group">
    <asp:Label ID="CheckoutLabel" runat="server" Text="Check-out Date"></asp:Label>
    <asp:TextBox ID="datetimepicker2" ClientIDMode="Static" runat="server" CSSclass="form-control"></asp:TextBox>
    </div>

Pictures of my two tables, so you can see how they look like:

enter image description here

The CheckIn and CheckOut datatype is: nchar(10) , have tried with "date" datatype but then it given me the following error "Conversion failed when converting date and/or time from character string."

enter image description here

The important thing here, is that if you tries to reserve a room and check-In or Check-Out date is a date between 15-07-2015 - 20-07-2015 then room 102 is reserved already and should not be shown.

Have you tried anything yourself? What did you do so far? And if you didn't, why do you expect people here to do the whole work for you? — Daniel Gruszczyk, Jul 13 '15 at 15:33

Tim Schmelter · Answer 1 · 2015-07-13T15:38:32.380

4

SELECT r.*
FROM Room r
WHERE NOT EXISTS
(
    SELECT 1 FROM Booking b
    WHERE b.RoomNumber = r.RoomNumber 
    AND 
    (
         (@CheckIn >= b.CheckIn AND @CheckIn  <= b.CheckOut)
      OR (@CheckIn <= b.CheckIn AND @Checkout >= b.CheckIn)
    )
)

edited Jul 13 '15 at 15:38

answered Jul 13 '15 at 15:26

Tim Schmelter

450,073
74
686
939

What do you mean with WHERE NOT EXISTS? – Raaydk Jul 13 '15 at 15:27
@Raaydk: it selects all rooms available because it excludes all which are reserved at that time. `NOT EXISTS` exludes the reserved. – Tim Schmelter Jul 13 '15 at 15:30
@Raaydk: but i have edited it a little bit. Withtout sample data it's still a little bit confusing. I hope you get the logic. – Tim Schmelter Jul 13 '15 at 15:36
Ye, i'm trying to do it know :) – Raaydk Jul 13 '15 at 15:38
@Raaydk: my current version should do it, i have used the parameter first since it is more readable. `@CheckIn` is the requested date and `b.CheckIn` is an already reserved checkin-date. – Tim Schmelter Jul 13 '15 at 15:40
You can reduce the checks to (@Checkin <= b.CheckOut AND @Checkout >= b.CheckIn). If that condition is true then there's an overlap. See this answer for how to check for interval overlap (https://stackoverflow.com/questions/3269434/whats-the-most-efficient-way-to-test-two-integer-ranges-for-overlap). – dcp Jun 19 '18 at 13:32

score 2 · Accepted Answer · answered Jul 13 '15 at 15:42

2

I think Tim misses one case -- using Between make the logic clearer maybe?

EDIT: Nevermind, Tim's is correct -- here is another way to do it.

the cases -- our checkin or checkout is sometime between when the room is used or our checkin and checkout "surround" when the room is used.

SELECT r.*
FROM Room r
WHERE NOT EXISTS
(
    SELECT 1 FROM Booking b
    WHERE b.RoomNumber = r.RoomNumber 
    AND 
    (
         @Checkin BETWEEN b.CheckIn AND b.CheckOut OR
         @Checkout BETWEEN b.CheckIn AND b.CheckOut OR 
         (@Checkin <= b.CheckIn AND @CheckOut >= b.CheckPut
    )
)

answered Jul 13 '15 at 15:42

Hogan

69,564
10
76
117

Have tried your code, but it does also not work.. it retrieve all rooms instead of letting room 102 be out of the table. – Raaydk Jul 13 '15 at 17:07
@Raaydk I'd need to see your actual code used and the sample data to see where your error is unless your date fields are stored as strings in which case you need to convert them to date type before you check them. – Hogan Jul 13 '15 at 17:22
Have updated now.. take a look. Also is it correct to have this line b.RoomNumber = r.RoomNumber.. because it should get all rooms which are not reserved. – Raaydk Jul 14 '15 at 09:43
This won't work unless you convert all the dates to date type before doing the comparison – Hogan Jul 14 '15 at 11:09
Yes.. i used CAST and changed the format from DD/MM/YYYY to YYYY/MM/DD and that worked! thank you very much... – Raaydk Jul 14 '15 at 14:13
You can reduce the checks to (@Checkin <= b.CheckOut AND @Checkout >= b.CheckIn). If that condition is true then there's an overlap. See this answer for how to check for interval overlap (https://stackoverflow.com/questions/3269434/whats-the-most-efficient-way-to-test-two-integer-ranges-for-overlap). – dcp Jun 19 '18 at 13:31

score 0 · Answer 3 · answered Apr 16 '19 at 12:11

CREATE PROCEDURE [dbo].[SP_RoomAvailabilty_Bind]
(
    @RTId bigint=null,/*Room Type*/
    @CheckInDate DATE=null,/*Checkin Date*/
    @CheckOutDate DATE=null/*Checkout Date*/
)
AS
BEGIN

SELECT tbl_RoomMaster.RoomSN, tbl_RoomTypeMaster.RoomType
FROM tbl_RoomMaster INNER JOIN
tbl_RoomTypeMaster ON tbl_RoomMaster.RTId = tbl_RoomTypeMaster.RTId 
WHERE tbl_RoomMaster.RoomId NOT IN (SELECT RoomId FROM tbl_Reservation WHERE CheckInDate=@CheckInDate)
AND tbl_RoomMaster.RTId=@RTId

SELECT tbl_RoomTypeMaster.RoomType, tbl_RoomMaster.RoomSN,tbl_RoomMaster.RoomId
FROM  tbl_RoomMaster INNER JOIN
tbl_RoomTypeMaster ON tbl_RoomMaster.RTId = tbl_RoomTypeMaster.RTId
WHERE tbl_RoomMaster.RoomId NOT IN (SELECT RoomId FROM tbl_Reservation 
WHERE (CheckInDate BETWEEn  @CheckInDate AND @CheckOutDate)
 OR  (CheckoutDate between @CheckInDate AND @CheckOutDate))

END

There are other answers that provide the OP's question, and they were posted many years ago. When posting an answer, please make sure you add either a new solution, or a substantially better explanation, especially when answering older questions. — help-info.de, Apr 16 '19 at 12:34

Checking available room resevation between check-in and check-out date with SQL in ASP.net

3 Answers3