0

i am trying to check availability of a room i have the following code:

  ALTER procedure [dbo].[CheckAvailability]
@checkIn datetime,
@checkout datetime
as
Select r.RoomName, RoomNO, [Description],Location,
 RoomPic, Price from [Room-Type] t ,  Room r, Booking b 
where r.TypeNo= t.TypeNo AND r.RoomName Not IN(
Select b.CheckinDate from Booking b where
(
@checkIn = b.CheckinDate
AND
@checkIn<b.CheckinDate OR
@checkout>b.CheckinDate
OR
@checkIn>b.CheckinDate OR
@checkout<b.CheckinDate 
AND 
[Status] != 'Booked'
)
)

the problem is now "it only show rooms that are not in the booking table only, wheres i would like to view the rooms in booking table but not between the date range"

2
Contributors
2
Replies
30
Views
3 Years
Discussion Span
Last Post by ChrisHunter
0

Use a JOIN clause to join information from more than one table.

This should show all rooms that are available on the specified dates.

SELECT r.RoomID, r.RoomNumber, r.RoomType, r.PricePerNight, b.CheckIn, b.CheckOut
FROM Rooms r
LEFT JOIN Bookings b on b.RoomID = r.RoomID
WHERE (Convert(datetime, b.CheckIn, 103) NOT BETWEEN Convert(datetime, @CheckInDate, 103) 
        AND Convert(datetime, @CheckOutDate, 103))
AND (Convert(datetime, b.CheckOut, 103) NOT BETWEEN Convert(datetime, @CheckInDate, 103) 
        AND Convert(datetime, @CheckOutDate, 103))

I hope this helps, let me know how you get on with this.

Here is a link about the different types of joins

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.