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

  ALTER procedure [dbo].[CheckAvailability]
@checkIn datetime,
@checkout datetime
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
@checkIn<b.CheckinDate OR
@checkIn>b.CheckinDate OR
[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"

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

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.18 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.