正在运行带有break T-SQL的值赋值

Question

使用下表的数据

支付金额日期2000年1月

客户		开票金额
1	100	60
1	100	40	2000年2月1日
2	200	150	2000/01
20	30	2000年2月1日
20	10	2000年3月1日
2	200	15	2000年4月1日

正如我在评论中提到的，这只是一个累积的SUM:

WITH YourTable AS(
SELECT *
FROM (VALUES(1,100,60 ,CONVERT(date,'01/01/2000')),
(1,100,40 ,CONVERT(date,'01/02/2000')),
(2,200,150,CONVERT(date,'   01/01/2000')),
(2,200,30 ,CONVERT(date,'01/02/2000')),
(2,200,10 ,CONVERT(date,'01/03/2000')),
(2,200,15 ,CONVERT(date,'01/04/2000')))V(Customer,AmountBilled,AmountPaid,[Date]))
SELECT Customer,
AmountBilled,
AmountPaid,
AmountBilled - SUM(AmountPaid) OVER (PARTITION BY Customer ORDER BY [Date] ASC
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Remainder,
[Date]
FROM YourTable
ORDER BY Customer,
[Date];

请注意，这将返回最后一行的-5，而不是5，作为200 - 205 = -5。如果您希望5将整个表达式包装在一个绝对函数中。

您也可以使用递归CTE来实现这一点。

DECLARE @customer table (Customer   int, AmountBilled int, AmountPaid int, PaidDate date)
insert into @customer
values
(1  ,100,   60  ,'01/01/2000')
,(1 ,100,   40  ,'01/02/2000')
,(2 ,200,   150 ,'01/01/2000')
,(2 ,200,   30  ,'01/02/2000')
,(2 ,200,   10  ,'01/03/2000')
,(2 ,200,   15  ,'01/04/2000');
;WITH CTE_CustomerRNK as
(
SELECT *, ROW_NUMBER() OVER(PARTITION BY customer order by paiddate) AS RNK
from @customer),
CTE_Customer as
(
SELECT customer, AmountBilled, AmountPaid, (amountbilled-amountpaid) as remainder, paiddate ,RNK FROM CTE_CustomerRNK  where rnk = 1
union all
SELECT r.customer, r.AmountBilled, r.AmountPaid, (c.remainder - r.AmountPaid) as remainder, r.PaidDate, r.rnk 
FROM CTE_CustomerRNK as r
inner join CTE_Customer as c
on c.Customer = r.Customer
and r.rnk = c.rnk + 1
)
SELECT customer, AmountBilled, AmountPaid, remainder, paiddate 
FROM CTE_Customer order by Customer

剩余2000-01-01<2000-01-02>

客户		已付款	付款
1	100	60	40
1	100	40	0	2000-01-02
2	200	150	2000-01-01
2	200	30	20
2	200	10	10		2000-01-03
2	200	15	2000-01-04