CODEWITHSUNDEEP
sqlasp.net
Raaydk
Raaydk

Reputation: 147

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

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.

Upvotes: 0

Views: 9258

Answers (3)

Code
Code

Reputation: 739

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

Upvotes: 0

Hogan
Hogan

Reputation: 70523

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
    )
)

Upvotes: 2

Tim Schmelter
Tim Schmelter

Reputation: 460138

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)
    )
)

Upvotes: 4

Related Questions