Sql-Server
計算逾期天數
我有一個類似於下面的數據集。我正在嘗試（但未能）做的是計算每筆付款在第一次付款和今天之間的任何一天的逾期天數。
但這裡有一個問題，每筆支付的金額必須先從最早的到期金額中扣除，然後剩餘的金額將從下一個到期金額中扣除。
因此，在下面的範例中，最早的逾期付款將是第 1 行，直到全額支付，即 26/08/16，然後第 4 行將成為最早的逾期付款。這是因為截至 2016 年 8 月 26 日的累計支付金額為 2656.15 英鎊，這將涵蓋第 1,2 和 3 行的應付金額。
RowNumber AccountNumber date AmountDue AmountPaid 1 1000 05/11/2015 1422.5 0 2 1000 26/11/2015 474.17 0 3 1000 26/12/2015 474.17 0 4 1000 26/01/2016 474.17 0 5 1000 26/02/2016 474.17 400.31 6 1000 26/03/2016 474.17 474.17 7 1000 26/04/2016 474.17 0 8 1000 26/05/2016 474.17 0 9 1000 26/06/2016 474.17 474.17 10 1000 26/07/2016 474.17 0 11 1000 26/08/2016 474.17 1307.5 12 1000 26/09/2016 474.17 0 13 1000 26/10/2016 474.17 0 14 1000 26/11/2016 474.17 434.17 15 1000 26/12/2016 474.17 434.17 16 1000 26/01/2017 474.17 0任何幫助將不勝感激！
我解決這個問題的方法是組裝這些元件：
- 使用帶有執行總計的 cte
AmountDue和AmountPaid- 用於使用
outer apply()執行總計獲取每個帳戶的最早付款日期和最後逾期天數
DaysOverDue是截至完全還清之日的最終逾期天數，或者如果尚未還清，@Date則在下面的查詢中將其設置為目前日期。
PaidDate是帳戶的第一個日期，其中RunningAmountPaid >= RunningAmountDue根據不在原始問題中的其他問題從評論中更新：
outer apply()在 中添加一個cte以檢查帳戶的下一行是否具有負值AmountPaid，如果是，則將其添加到RunningAmountPaid.這解決了當下一筆交易是前一筆付款的撤銷時金額被標記為已付款的問題。
rextester 連結：http ://rextester.com/YTHG73937
測試設置：
create table t ( RowNumber int , AccountNumber int , [date] date , AmountDue decimal(9,2) , AmountPaid decimal(9,2) ); insert into t values (1,1000,'2015-11-05',474.17,474.17) ,(2,1000,'2015-11-26',474.17,474.17) ,(3,1000,'2015-12-26',474.17,474.17) ,(4,1000,'2015-12-27',0,-474.17) ,(5,1000,'2016-01-26',474.17,0) ,(6,1000,'2016-02-26',474.17,0) ,(7,1000,'2016-03-26',474.17,474.17) ,(8,1000,'2016-04-26',474.17,0) ,(9,1000,'2016-05-26',474.17,0) ,(10,1000,'2016-06-26',474.17,474.17) ,(11,1000,'2016-07-26',474.17,0) ,(12,1000,'2016-08-26',474.17,1307.5) ,(13,1000,'2016-09-26',474.17,0) ,(14,1000,'2016-10-26',474.17,0) ,(15,1000,'2016-11-26',474.17,434.17) ,(16,1000,'2016-12-26',474.17,-434.17) ,(17,1000,'2017-01-26',474.17,0);詢問：
declare @Date date = convert(date,getdate()); with cte as ( select * , RunningAmountDue = sum(AmountDue) over (order by date) , RunningAmountPaid= sum(AmountPaid) over (order by date) + isnull(x.Reversal,0) , AccountBalance = sum(AmountDue) over (order by date) - sum(AmountPaid) over (order by date) from t outer apply ( select top 1 Reversal = i.AmountPaid from t as i where i.AccountNumber = t.AccountNumber and i.RowNumber = t.RowNumber+1 and i.AmountPaid < 0 ) as x ) select o.RowNumber , o.AccountNumber , Date = convert(varchar(10),o.Date,120) , AmountDue = convert(varchar(30),o.AmountDue,1) , AmountPaid = convert(varchar(30),o.AmountPaid,1) , RunningAmountDue = convert(varchar(30),o.RunningAmountDue,1) , RunningAmountPaid = convert(varchar(30),o.RunningAmountPaid,1) , AccountBalance = convert(varchar(30),o.AccountBalance,1) , PaidDate = convert(varchar(10),x.PaidDate,120) , DaysOverDue = datediff(day,o.Date,isnull(x.PaidDate,@Date)) from cte o outer apply ( select top 1 PaidDate = i.date from cte i where i.AccountNumber=o.AccountNumber and i.RunningAmountPaid>=o.RunningAmountDue order by i.date asc ) as x;結果：
+-----------+---------------+------------+-----------+------------+------------------+-------------------+----------------+------------+-------------+ | RowNumber | AccountNumber | Date | AmountDue | AmountPaid | RunningAmountDue | RunningAmountPaid | AccountBalance | PaidDate | DaysOverDue | +-----------+---------------+------------+-----------+------------+------------------+-------------------+----------------+------------+-------------+ | 1 | 1000 | 2015-11-05 | 474.17 | 474.17 | 474.17 | 474.17 | 0.00 | 2015-11-05 | 0 | | 2 | 1000 | 2015-11-26 | 474.17 | 474.17 | 948.34 | 948.34 | 0.00 | 2015-11-26 | 0 | | 3 | 1000 | 2015-12-26 | 474.17 | 474.17 | 1422.51 | 948.34 | 0.00 | 2016-03-26 | 91 | | 4 | 1000 | 2015-12-27 | 0.00 | -474.17 | 1422.51 | 948.34 | 474.17 | 2016-03-26 | 90 | | 5 | 1000 | 2016-01-26 | 474.17 | 0.00 | 1896.68 | 948.34 | 948.34 | 2016-06-26 | 152 | | 6 | 1000 | 2016-02-26 | 474.17 | 0.00 | 2370.85 | 948.34 | 1422.51 | 2016-08-26 | 182 | | 7 | 1000 | 2016-03-26 | 474.17 | 474.17 | 2845.02 | 1422.51 | 1422.51 | 2016-08-26 | 153 | | 8 | 1000 | 2016-04-26 | 474.17 | 0.00 | 3319.19 | 1422.51 | 1896.68 | NULL | 275 | | 9 | 1000 | 2016-05-26 | 474.17 | 0.00 | 3793.36 | 1422.51 | 2370.85 | NULL | 245 | | 10 | 1000 | 2016-06-26 | 474.17 | 474.17 | 4267.53 | 1896.68 | 2370.85 | NULL | 214 | | 11 | 1000 | 2016-07-26 | 474.17 | 0.00 | 4741.70 | 1896.68 | 2845.02 | NULL | 184 | | 12 | 1000 | 2016-08-26 | 474.17 | 1307.50 | 5215.87 | 3204.18 | 2011.69 | NULL | 153 | | 13 | 1000 | 2016-09-26 | 474.17 | 0.00 | 5690.04 | 3204.18 | 2485.86 | NULL | 122 | | 14 | 1000 | 2016-10-26 | 474.17 | 0.00 | 6164.21 | 3204.18 | 2960.03 | NULL | 92 | | 15 | 1000 | 2016-11-26 | 474.17 | 434.17 | 6638.38 | 3204.18 | 3000.03 | NULL | 61 | | 16 | 1000 | 2016-12-26 | 474.17 | -434.17 | 7112.55 | 3204.18 | 3908.37 | NULL | 31 | | 17 | 1000 | 2017-01-26 | 474.17 | 0.00 | 7586.72 | 3204.18 | 4382.54 | NULL | 0 | +-----------+---------------+------------+-----------+------------+------------------+-------------------+----------------+------------+-------------+