BookingID RoomCode ArrivalDate DepartureDate
100033 RM-3 16/01/2013 00:00 18/01/2013 00:00
100034 RM-1 16/01/2013 00:00 22/01/2013 00:00
100035 RM-1 27/01/2013 00:00 29/01/2013 00:00
100036 RM-14 20/01/2013 00:00 27/01/2013 00:00
100037 RM-5 27/01/2013 00:00 28/01/2013 00:00
100038 RM-6a 15/01/2013 00:00 20/01/2013 00:00
CREATE TABLE #TEMPTABLE
(bookingID INT,
RoomCode VARCHAR(10),
ArrivalDate DATETIME,
DepartureDate DATETIME)
DECLARE @startdate DATETIME
DECLARE @enddate DATETIME
DECLARE @date DATETIME
SET DATEFORMAT dmy
SET @startdate = '19/01/2013'
SET @enddate = '21/01/2013'
SET @date = @startdate
WHILE @date <= @enddate
BEGIN
INSERT INTO #temptable SELECT * FROM bookings
WHERE @date BETWEEN arrivaldate AND departuredate
SET @date = DATEADD(d,1,@date)
END
SELECT DISTINCT * FROM #temptable
declare @temp table (bookingid int, roomcode varchar(5), arrivaldate datetime, departuredate datetime)
insert into @temp values('100033', 'RM-3', '01/16/2013', '01/18/2013')
insert into @temp values('100034', 'RM-1', '01/16/2013', '01/22/2013')
insert into @temp values('100035', 'RM-1', '01/27/2013', '01/29/2013')
insert into @temp values('100036', 'RM-14', '01/20/2013', '01/27/2013')
insert into @temp values('100037', 'RM-5', '01/27/2013', '01/28/2013')
insert into @temp values('100038', 'RM-6a', '01/15/2013', '01/20/2013')
declare @startdate as datetime
declare @enddate as datetime
set @startdate = '01/17/2013'
set @enddate = '01/21/2013'
declare @tally table (Dt datetime)
WHILE @startdate <= @enddate
Begin
insert into @tally values (@startdate)
set @startdate = dateadd(d,1,@startdate)
End
select
distinct a.bookingid, a.roomcode, a.arrivaldate, a.departuredate
from @temp a, @tally b where b.dt between a.arrivaldate and a.departuredate
Select * FROM booking_table
WHERE ArrivalDate <= [WindowEndDate]
AND DepartureDate >= [WindowStartDate]