Finding availability by date 1

[thumper808]

Hi everyone,
I've been racking my brains for days on my problem. I have a table called ROOMBOOKINGS and some of it's fields are RoomNumber, BookingStartDate and BookingEndDate. The RoomNumber is a foreign key to the ROOMS table which has the RoomNumber,Description,and RoomRate.
All I want to do is select all the rooms which are available to book.
So what I did was this:
create procedure FindRoomAvailabilitybyDate (@StartDate datetime, @EndDate datetime)
as
select * from rooms
where not roomnumber in
(select roombookings.roomnumber from roombookings
where bookingstartdate between @StartDate and @EndDate)

but this only gives me the rooms available if I enter exact dates.
For example, if an existing booking is for 11/1/2002 to 11/6/2002, and I enter those dates respectively, the procedure works like a charm. BUT if I enter a starting date of 11/2/2002 and 11/5/2002 for an ending date into the SP, it doesn't work. Please forgive my long winded explanation but I'm kinda new to sql and I did look on the existing threads before I wrote to you all.
I guess I want to know how to do the 'catch all' for a problem like this.
Thank you very much in advance.

Thumper (-:

 

[MeanGreen]

Ok.. Lets say your checkout time is 12:00. You also have to remember that when you book your rooms, append the time as well to these bookings.

select @startdate = convert(datetime,convert(varchar(12),@startdate,101) + ' 12:00:00')

select @enddate = convert(datetime,convert(varchar(12),dateadd(dd,+1,@enddate),101) + ' 11:59:59')

select * from rooms
where roomnumber not in
(select roomnumber from roombookings
where bookingstartdate between @StartDate and @EndDate)

Hope this helps.

 

[thumper808]

Hi MeanGreen,
You brought up a VERY important business rule that I forgot to implement, the checkout time. THANK YOU so much! I would've had to explain to the big guys why errors pertaining to that were happening.

My SP is still not catching the 11/02/2002 to 11/05/2002 however... I'm thinking it's in the BETWEEN part, is there another clause I can put in there to catch all the dates already booked? You've already helped me greatly, MeanGreen. Any additional insight would be greatly appreciated. Thanks again!

Thumper.

 

[MeanGreen]

Can you give me your sample data to see why? I have set up both tables, and it seems to work fine. I know that 11/2/2002 is before today, but the between should still work.

Thank you.

 

[thumper808]

Hi MeanGreen,

roomnumber bookingstartdate bookingendate
1 11/01/2002 11/06/2002
2 11/08/2002 11/16/2002
1 11/08/2002 11/16/2002
1 11/20/2002 11/30/2002
10 11/08/2002 11/16/2002

This is exactly the test data I'm using.
So when I try to check availablility for a start date of 11/09/2002 and 11/17/2002 for room 1, sql still returns room 1 as an available date.
I'm sorry, I'm new at this forum stuff. If you meant something else, please specify again. Thanks again, MeanGreen.

 

[MeanGreen]

This will fix your problem.

select * from rooms
where roomnumber not in
(select roomnumber from roombookings
where (@startdate between Bookingstartdate and bookingenddate)
or (@enddate between bookingstartdate and bookingenddate)
)

Hope this helps.

 

[thumper808]

Man, are you kidding? You're awesome! You did more than help, you solved the dang thing! AND you helped me with the checkout issue. I can't say enough. Thank you very much MeanGreen. I don't know what time it is where you are but it's 11:45 p.m. and I am a happy camper thanks to you. Hey, once again... U rock! Thanks, you get two thumbs up!


Thumper