I have a RoomAvailability table for a Hotel booking engine. Here's some sample data.
1 6/24/2008 12:00:00 AM 0
1 6/25/2008 12:00:00 AM 1
1 6/26/2008 12:00:00 AM 0
1 6/27/2008 12:00:00 AM 0
Column 1 is the RoomNumber, Column 2 is a date, column 3 is a ReservationID.
Therefore, if someone needed to check in on 6/24 and check out 6/27 this particular room should not show up as a potential match because it is booked on 6/25. So basically, I need a query that returns room numbers where that room has no reservationID on any day from the check-in to check-out dates.
I'm having a heck of a time thinking of a simple solution for this. I can think of less elegant ways to bring back the correct data but I have a feeling that there is some T-Sql function or some recursive joining or something that will get me where i want to get.
I've attached my code so far. Of course it doesn't work. Anyone got any good ideas?
where datediff(day,'06/24/2008',Date) >= 0 and
datediff(day,Date,'06/27/2008') >= 0
and ReservationID = 0
group by RoomNumber