Check Availability

[Sizwe_1]

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"

[ChrisHunter]

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

[ChrisHunter]

Hi Sizwe, how did you get on with this problem? did you manage to solve it?