SQL Server Performance Forum – Threads Archive

Get run of consecutive dates within a range

I have a problem trying to figure out a query. I need to pull a run of consecutive dates within a range of dates. Say I have the following tables: tblReservations
ReservationPK int
GuestID int
RoomPK int
ReservedFrom datetime
ReservedThrough datetime tblRooms
RoomPK int
RoomNumber varchar(5)
RoomType int In my stored procedure I will pass in variables for ReservationStartDate (datetime) & ReservationEndDate (datetime). How would I find the rooms that are available for reservation for the consecutive dates 05/01/2006 through 05/05/2006? I am stumped on this one…
Thank you!
Michael

---

The procedure uses a subquery to eliminate all rooms that are FULLY or partially booked between the 2 dates. All the other rooms (available between the 2 dates) are returned.
CREATE PROC AvailableRooms
@ReservationStartDate datetime,
@ReservationEndDate datetime
AS
SELECT RoomNumber,RoomType FROM tblRooms WHERE RoomPK NOT IN(
SELECT rm.RoomPK FROM tblRooms rm JOIN tblReservations rs
ON rm.RoomPK=rs.RoomPK
WHERE rs.ReservedFrom<[email protected] AND rs.ReservedThrough>[email protected] )
GO
Nathan H. Omukwenyi
MVP [SQL Server]

---

]]>