I have a price schedule table for rooms Day by Day. My table like below.
RoomId Date(DD.MD.YY) RemainingQuota Price
-----------|------------------|--------------------|----------
1 1.5.2017 5 10.00
1 1.6.1017 0 10.00
1 1.7.1017 3 12.00
1 1.8.1017 0 10.00
1 1.9.1017 6 15.00
.....
.....
Input params are
@CheckIn date = CAST('1.5.2017' AS date)
@CheckOut date = CAST('1.8.2017' AS date)
I need to select 3 consecutive dates which have RemainingQuota greater then zero and first date closest @CheckIn
I know the CTE recursive solution. However it comes me very expensive. Does any one know a cheap solution?
Thank you.
I have solved the case by myself without using CTE. Here is complate test code.
BEGIN /*PREPARE SAMPLE DATA*/
CREATE TABLE #priceSchedules(
RoomId int not null,
[Date] date not null,
Quota int not null,
Price decimal(18,4) not null
)
INSERT INTO #priceSchedules
select
RoomId = R.RoomId
,[Date] = D.[Date]
,Quota = abs(checksum(NewId()) % 5)
,Price = abs(checksum(NewId()) % 50)
from
(
select top 100
RoomId = row_number() over(order by R.number)
from master..spt_values R
) R
OUTER apply(
select top 60
[Date] = cast(DATEADD(day, row_number() over(order by t1.number), getdate()) as date)
from master..spt_values t1
) D
END
BEGIN /*PARAMETERS*/
DECLARE @RoomId int = 5,
@CheckIn date = cast(dateadd(day, 3, getdate()) as date),
@CheckOut date = cast(dateadd(day, 6, getdate()) as date);
DECLARE @TotalNight int = datediff(day, @CheckIn, @CheckOut)
END
BEGIN /*QUERY */
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
SET STATISTICS TIME ON; --FOR CHECK PERFORMANCE
PRINT('-----------START SELECT---------------------------------')
SELECT
*
FROM
(
SELECT
_P.*
,RowNo = ROW_NUMBER() OVER(PARTITION BY RoomId ORDER BY DayDiffFromCheckIn)
FROM(
SELECT
P.RoomId
,StartDate = P.[Date]
,EndDate = MAX(P2.[Date])
,CalcDay = COUNT(*)
,DayDiffFromCheckIn = CAST(ABS(DATEDIFF(day,P.[Date],@CheckIn)) AS INT)
,MinQuota = IIF(MIN(P.Quota) > MIN(P2.Quota) ,MIN(P2.Quota),MIN(P.Quota))
,TotalPrice = MIN(P.Price) + SUM(P2.Price)
FROM #priceSchedules P
JOIN #priceSchedules AS P2 ON P2.RoomId = P.RoomId AND P2.[Date] > P.[Date] AND P2.[Date] < DATEADD(day, @TotalNight ,P.[Date]) AND P2.Quota > 0
WHERE P.RoomId = @RoomId AND P.Quota > 0 AND P.[Date] >= GETDATE()
GROUP BY P.RoomId,P.[Date]
HAVING(COUNT(*) = (@TotalNight) - 1) -- -1 is for first row
) _P
)__P
WHERE __P.RowNo < 3 -- for top 3 option colsest to @CheckIn date
PRINT('-----------END SELECT---------------------------------')
--SELECT * FROM #priceSchedules
DROP TABLE #priceSchedules
SET STATISTICS TIME OFF;
END
I have solved the case by myself without using CTE. Here is complate test code.
BEGIN /*PREPARE SAMPLE DATA*/
CREATE TABLE #priceSchedules(
RoomId int not null,
[Date] date not null,
Quota int not null,
Price decimal(18,4) not null
)
INSERT INTO #priceSchedules
select
RoomId = R.RoomId
,[Date] = D.[Date]
,Quota = abs(checksum(NewId()) % 5)
,Price = abs(checksum(NewId()) % 50)
from
(
select top 100
RoomId = row_number() over(order by R.number)
from master..spt_values R
) R
OUTER apply(
select top 60
[Date] = cast(DATEADD(day, row_number() over(order by t1.number), getdate()) as date)
from master..spt_values t1
) D
END
BEGIN /*PARAMETERS*/
DECLARE @RoomId int = 5,
@CheckIn date = cast(dateadd(day, 3, getdate()) as date),
@CheckOut date = cast(dateadd(day, 6, getdate()) as date);
DECLARE @TotalNight int = datediff(day, @CheckIn, @CheckOut)
END
BEGIN /*QUERY */
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
SET STATISTICS TIME ON; --FOR CHECK PERFORMANCE
PRINT('-----------START SELECT---------------------------------')
SELECT
*
FROM
(
SELECT
_P.*
,RowNo = ROW_NUMBER() OVER(PARTITION BY RoomId ORDER BY DayDiffFromCheckIn)
FROM(
SELECT
P.RoomId
,StartDate = P.[Date]
,EndDate = MAX(P2.[Date])
,CalcDay = COUNT(*)
,DayDiffFromCheckIn = CAST(ABS(DATEDIFF(day,P.[Date],@CheckIn)) AS INT)
,MinQuota = IIF(MIN(P.Quota) > MIN(P2.Quota) ,MIN(P2.Quota),MIN(P.Quota))
,TotalPrice = MIN(P.Price) + SUM(P2.Price)
FROM #priceSchedules P
JOIN #priceSchedules AS P2 ON P2.RoomId = P.RoomId AND P2.[Date] > P.[Date] AND P2.[Date] < DATEADD(day, @TotalNight ,P.[Date]) AND P2.Quota > 0
WHERE P.RoomId = @RoomId AND P.Quota > 0 AND P.[Date] >= GETDATE()
GROUP BY P.RoomId,P.[Date]
HAVING(COUNT(*) = (@TotalNight) - 1) -- -1 is for first row
) _P
)__P
WHERE __P.RowNo < 3 -- for top 3 option colsest to @CheckIn date
PRINT('-----------END SELECT---------------------------------')
--SELECT * FROM #priceSchedules
DROP TABLE #priceSchedules
SET STATISTICS TIME OFF;
END