使用递归上下文更新游标

Question

在我上一个问题之后，我正在尝试更新光标以使用递归上下文。然而，这似乎并不简单。我无法让递归上下文循环浏览我的折扣并分配相关数量以完全覆盖我的预订。

ALTER FUNCTION [discount].[fn_get_eligible_packages]
(
@TecTacClientId NVARCHAR(255),
@StartDate      DATETIME2,
@EndDate        DATETIME2,
@ResourceId     INT,
@BookingId      INT NULL
)
RETURNS @discounts TABLE 
(
[DiscountId]    UNIQUEIDENTIFIER,
[Percentage]    INT,
[StartDate]     DATETIME2,
[EndDate]       DATETIME2,
[CoveredQty]    DECIMAL(10,2)
)
AS
BEGIN
IF DAY(@StartDate) != DAY(@EndDate) 
OR DATEDIFF(MINUTE, @StartDate, @EndDate) <= 0 
OR DATEDIFF(MINUTE, @StartDate, @EndDate) >= 1440 RETURN
DECLARE @Qty DECIMAL(10,2), @RequiredQty DECIMAL(10,2) = DATEDIFF(MINUTE, @StartDate, @EndDate) / 60.0
DECLARE @DiscountId UNIQUEIDENTIFIER, @Percentage INT, @AvailableQty DECIMAL(10,2)
DECLARE curs CURSOR FOR 
SELECT DiscountId, [Percentage], AvailableQty 
(...)
OPEN curs
FETCH NEXT FROM curs INTO @DiscountId, @Percentage, @AvailableQty 
WHILE @@FETCH_STATUS = 0  
BEGIN  
IF @RequiredQty = 0 RETURN
IF @RequiredQty > @AvailableQty  SET @Qty = @AvailableQty
IF @AvailableQty >= @RequiredQty SET @Qty = @RequiredQty
INSERT INTO @discounts 
VALUES (@DiscountId, @Percentage, @StartDate, DATEADD(MINUTE, @Qty*60, @StartDate), @Qty)
SET @StartDate = DATEADD(MINUTE, @Qty*60, @StartDate)
SET @RequiredQty -= @Qty
FETCH NEXT FROM curs INTO @DiscountId, @Percentage, @AvailableQty
END
CLOSE curs
DEALLOCATE curs
RETURN
END

根据这里提出的答案，我尝试了以下代码

DECLARE @StartDate DATETIME2    = '2018-03-14 10:00:00'
DECLARE @EndDate DATETIME2      = '2018-03-14 12:00:00'
;WITH CTE (DiscountId, [Percentage], AvailableQty, Qty) AS  
(
SELECT 
'f4156db3-a0e3-4324-acf7-04cf2f37325e' AS DiscountId, 
100 AS [Percentage], 
10 AS AvailableQty, 
CAST(DATEDIFF(MINUTE, @StartDate, @EndDate) / 60.0 AS DECIMAL(10,2)) AS Qty 
UNION ALL
SELECT 
'4f351cda-443a-4d6a-9265-1ea70af0536d' AS DiscountId, 
100 AS [Percentage], 
10 AS AvailableQty, 
CAST(DATEDIFF(MINUTE, @StartDate, @EndDate) / 60.0 AS DECIMAL(10,2)) AS Qty 
UNION ALL
SELECT 
'51846222-7432-43f7-8647-d2a8e70ea3cf' AS DiscountId, 
100 AS [Percentage], 
10 AS AvailableQty, 
CAST(DATEDIFF(MINUTE, @StartDate, @EndDate) / 60.0 AS DECIMAL(10,2)) AS Qty 
---------------------------------------------------------------------------------       
UNION ALL
SELECT 
DiscountId,
[Percentage],
AvailableQty,
CAST(Qty - 
CASE 
WHEN Qty > AvailableQty THEN AvailableQty
ELSE Qty
END AS DECIMAL(10,2))
FROM CTE
WHERE Qty < 0
)
SELECT DiscountId, Percentage, Qty FROM CTE

例如，假设我们有以下上下文

• 预订3小时
• 折扣 1 有可用数量 = 1 小时
• 折扣 2 有可用数量 = 0.5 小时
• 折扣 3 有可用数量 = 10 小时

预期输出

Discount 1 > 1 hour
Discount 2 > 0.5 hour
Discount 3 > 1.5 hours
This fully covers my 3 hours long booking

另一个例子

• 预订1小时
• 折扣 1 有可用数量 = 1 小时
• 折扣 2 有可用数量 = 0.5 小时
• 折扣 3 有可用数量 = 10 小时

预期输出

Discount 1 > 1 hour
This fully covers my 1 hour long booking

另一个例子

• 预订10小时
• 折扣 1 有可用数量 = 1 小时
• 折扣 2 有可用数量 = 1 小时
• 折扣 3 有可用数量 = 1 小时

预期输出

- Discount 1 has 1 hour remaining 
- Discount 2 has 1 hour remaining 
- Discount 3 has 1 hour remaining
This fully does NOT cover my 10 hours long booking. Only 3 hours will be covered by a discount

目前，对于两个小时的预订，我得到以下内容：

DiscountId                              Percentage  Qty
f4156db3-a0e3-4324-acf7-04cf2f37325e    100         2.00
4f351cda-443a-4d6a-9265-1ea70af0536d    100         2.00
51846222-7432-43f7-8647-d2a8e70ea3cf    100         2.00

而不是：

DiscountId                              Percentage  CoveredQty
f4156db3-a0e3-4324-acf7-04cf2f37325e    100         2.00

问题是由此 sql 引起的

CAST(DATEDIFF(MINUTE, @StartDate, @EndDate) / 60.0 AS DECIMAL(10,2)) AS Qty 

我不应该通过我所有的折扣来传递数量。相反，我应该保留一个共享变量并递减它。不幸的是，我无法同时设置变量和选择...

有什么好主意吗？

Answer 1

此查询不使用cursor或recursive cte。查询中的最后一列计算每个折扣涵盖的分钟数。您尚未显示表结构，因此我创建了两个包含折扣和预订的表。

declare @discount table (id int, name varchar(100), duration int)
declare @booking table (id int, startDate datetime, endDate datetime)
insert into @discount 
values (1, 'Discount 1', 60)
, (2, 'Discount 2', 30)
, (3, 'Discount 3', 600)
insert into @booking
values (1, '20180314 10:00', '20180314 13:00')
, (2, '20180314 10:00', '20180314 11:00')
, (3, '20180314 10:00', '20180316 13:00')
, (4, '20180314 10:00', '20180314 10:20')
, (5, '20180314 10:00', '20180314 11:20')
;with cte as (
select
*, rTotal = isnull(sum(duration) over (order by id rows between unbounded preceding and 1 preceding), 0)
from 
@discount
)
select
*
from (
select 
a.id, a.startDate, a.endDate, b.name
, iTime = iif(c.diff >= b.rTotal + b.duration, duration, iif(c.diff <= 0, 0, diff))
from 
@booking a
cross join cte b
cross apply (select diff = datediff(mi, a.startDate, a.endDate) - b.rTotal) c
) t
where
iTime > 0
order by 1