Member Avatar for 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"

  • 2 Contributors
  • 2 Replies
  • 242 Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by ChrisHunter
Member Avatar for 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

Member Avatar for ChrisHunter

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 developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.