sql-server - SQL Server 动态重置运行余额

Question

我当前的问题是我有一个运行余额，其中一个值低于另一个运行余额需要重置。但不仅要重置，还要用另一个值作为起始值，重新开始平衡。

下面是包含数据的表格:

+-------------+--------+---------------------+-------------------+--------------+-------------+
| Tran_DateSK | Amount | Running_AccountFees | Overlimit_Balance | Restart_Calc | Actual_Calc |
+-------------+--------+---------------------+-------------------+--------------+-------------+
|    20200217 |     39 |                  39 |           3867.76 |            0 |          39 |
|    20200217 |     50 |                  89 |           3867.76 |            0 |          89 |
|    20200316 |     39 |                 128 |           4735.52 |            0 |         128 |
|    20200316 |     50 |                 178 |           4735.52 |            0 |         178 |
|    20200324 |     50 |                 228 |           2685.52 |            0 |         228 |
|    20200330 |     50 |                 278 |             49.52 |            1 |       49.52 |
|    20200415 |     39 |                 317 |             49.52 |            1 |       49.52 |
|    20200515 |     39 |                 356 |           3917.28 |            0 |       88.52 |
|    20200515 |     50 |                 406 |           3917.28 |            0 |      138.52 |
|    20200519 |     50 |                 456 |           3467.28 |            0 |      188.52 |
|    20200604 |     50 |                 506 |           3017.28 |            0 |      238.52 |
|    20200609 |     50 |                 556 |           2167.28 |            0 |      288.52 |
|    20200611 |     50 |                 606 |             49.28 |            1 |       49.28 |
|    20200615 |     39 |                 645 |           3917.04 |            0 |       88.28 |
|    20200615 |     50 |                 695 |           3917.04 |            0 |      138.28 |
|    20200616 |     50 |                 745 |           3017.04 |            0 |      188.28 |
|    20200616 |     50 |                 795 |           3017.04 |            0 |      238.28 |
|    20200619 |     50 |                 845 |           2567.04 |            0 |      288.28 |
|    20200624 |     50 |                 895 |             47.04 |            1 |       47.04 |
|    20200715 |     39 |                 934 |             47.04 |            1 |       47.04 |
+-------------+--------+---------------------+-------------------+--------------+-------------+

实际计算是期望的结果，而运行帐户费用是问题。

流水账户费用是“金额”的流水余额，overlimit_balance是测试。我们需要确保 running_accountfees 不大于超限，

如果是，则取超限值并通过再次添加金额重新开始计算。

产生这个的我的查询:

    SELECT 
    [Transaction].ReportDateSK                              AS 'Tran_DateSK'
    ,[Transaction].AmountChange/100.00                      AS 'Amount'
    ,SUM([Transaction].AmountChange/100.00) 
        OVER (PARTITION BY [Transaction].AccountSK
        ORDER BY [Transaction].ReportDateSK
        ROWS BETWEEN UNBOUNDED PRECEDING AND 0 PRECEDING)   AS 'Running_AccountFees'
    ,[Summary].Overlimit_Balance                            AS 'Overlimit_Balance'
    ,CASE 
        WHEN SUM([Transaction].AmountChange/100.00) 
                OVER (PARTITION BY [Transaction].AccountSK
                ORDER BY [Transaction].ReportDateSK
                ROWS BETWEEN UNBOUNDED PRECEDING AND 0 PRECEDING) > [Summary].Overlimit_Balance
        THEN 1
        ELSE 0
    END                                                     AS 'Restart_Calc'
    ,''                                                     AS 'Actual_Calc'
FROM  
    Fact.[Transaction] [Transaction]
    INNER JOIN Fact.AccountSummary [Summary] ON [Summary].DateSK = [Transaction].ReportDateSK
        AND [Summary].AccountSK = [Transaction].AccountSK
        AND [Summary].[Current] = 1
WHERE IsFeeTransaction = 1
    AND [Transaction].AccountSK = 725
    AND [Transaction].ReportDateSK BETWEEN 20200217 AND 20200730

Answer 1

意识到您问题中的数据是本质上是源数据，并且能够得出以下结果。它不是很漂亮，但它提供了正确的输出。在评论中解释了它是如何工作的:

declare @t table(Tran_DateSK int, Amount decimal(10,2), Running_AccountFees int, Overlimit_Balance decimal(10,2), Restart_Calc bit, Actual_Calc decimal(10,2));
insert into @t values(20200217,39,39,3867.76,0,39),(20200217,50,89,3867.76,0,89),(20200316,39,128,4735.52,0,128),(20200316,50,178,4735.52,0,178),(20200324,50,228,2685.52,0,228),(20200330,50,278,49.52,1,49.52),(20200415,39,317,49.52,1,49.52),(20200515,39,356,3917.28,0,88.52),(20200515,50,406,3917.28,0,138.52),(20200519,50,456,3467.28,0,188.52),(20200604,50,506,3017.28,0,238.52),(20200609,50,556,2167.28,0,288.52),(20200611,50,606,49.28,1,49.28),(20200615,39,645,3917.04,0,88.28),(20200615,50,695,3917.04,0,138.28),(20200616,50,745,3017.04,0,188.28),(20200616,50,795,3017.04,0,238.28),(20200619,50,845,2567.04,0,288.28),(20200624,50,895,47.04,1,47.04),(20200715,39,934,47.04,1,47.04);

with t as
(
    select Tran_DateSK
          ,Amount
          -- Check if the Running_AccountFees are over the Overlimit_Balance
          ,case when sum(Amount) over (order by Tran_DateSK,Amount,Overlimit_Balance rows unbounded preceding) > Overlimit_Balance
                        -- If so, check if the Running_AccountFees in the previous row were also over the Overlimit_Balance
                  then case when (sum(Amount) over (order by Tran_DateSK,Amount,Overlimit_Balance rows unbounded preceding) - Amount) > lag(Overlimit_Balance,1,0) over (order by Tran_DateSK,Amount,Overlimit_Balance)
                            then 0      -- and in those instances this means multiple Restart_Calcs in a row, so set the Amount to zero as we don't want to increase the fees when calculating the Actual_Calc
                            else Amount
                            end
              else Amount
              end as Amount_Adj

          ,sum(Amount) over (order by Tran_DateSK,Amount,Overlimit_Balance rows unbounded preceding) as Running_AccountFees
          
          ,lag(Overlimit_Balance,1,0) over (order by Tran_DateSK,Amount,Overlimit_Balance) as Prev_Overlimit_Balance
          
          ,Overlimit_Balance

          ,case when sum(Amount) over (order by Tran_DateSK,Amount,Overlimit_Balance rows unbounded preceding) > Overlimit_Balance
                then 1
                else 0
                end as Restart_Calc
    from @t
)
,b as
(
    select *
          ,case when Running_AccountFees > Overlimit_Balance    -- If this row is the first in a possible series of balance resets
                    and sum(Amount_Adj) over (order by Tran_DateSK,Amount,Overlimit_Balance rows between unbounded preceding and 1 preceding) <= Prev_Overlimit_Balance
                then Overlimit_Balance      -- Take the Overlimit_Balance and subtract the *Adjusted* Running_AccountFees
                     - sum(Amount_Adj) over (order by Tran_DateSK,Amount,Overlimit_Balance rows  between unbounded preceding and 1 preceding)
                     - Amount_Adj
                else 0
                end as Reset_Bal
    from t
)
select Tran_DateSK
      ,Amount
      ,Running_AccountFees
      ,Overlimit_Balance
      ,Restart_Calc
      -- For each *Adjusted* Running_AccountFees, apply the most negative Reset_Bal value, as this will contain the entire amount that needs to be reset from the current *Adjusted* Running_AccountFees to get the correct Balance_Calc
      ,sum(Amount_Adj) over (order by Tran_DateSK,Amount,Overlimit_Balance rows unbounded preceding)
        + min(Reset_Bal) over (order by Tran_DateSK,Amount,Overlimit_Balance rows unbounded preceding)
        as Balance_Calc
from b
order by Tran_DateSK;

输出

+-------------+--------+---------------------+-------------------+--------------+--------------+
| Tran_DateSK | Amount | Running_AccountFees | Overlimit_Balance | Restart_Calc | Balance_Calc |
+-------------+--------+---------------------+-------------------+--------------+--------------+
|    20200217 |  39.00 |               39.00 |           3867.76 |            0 |        39.00 |
|    20200217 |  50.00 |               89.00 |           3867.76 |            0 |        89.00 |
|    20200316 |  39.00 |              128.00 |           4735.52 |            0 |       128.00 |
|    20200316 |  50.00 |              178.00 |           4735.52 |            0 |       178.00 |
|    20200324 |  50.00 |              228.00 |           2685.52 |            0 |       228.00 |
|    20200330 |  50.00 |              278.00 |             49.52 |            1 |        49.52 |
|    20200415 |  39.00 |              317.00 |             49.52 |            1 |        49.52 |
|    20200515 |  39.00 |              356.00 |           3917.28 |            0 |        88.52 |
|    20200515 |  50.00 |              406.00 |           3917.28 |            0 |       138.52 |
|    20200519 |  50.00 |              456.00 |           3467.28 |            0 |       188.52 |
|    20200604 |  50.00 |              506.00 |           3017.28 |            0 |       238.52 |
|    20200609 |  50.00 |              556.00 |           2167.28 |            0 |       288.52 |
|    20200611 |  50.00 |              606.00 |             49.28 |            1 |        49.28 |
|    20200615 |  39.00 |              645.00 |           3917.04 |            0 |        88.28 |
|    20200615 |  50.00 |              695.00 |           3917.04 |            0 |       138.28 |
|    20200616 |  50.00 |              745.00 |           3017.04 |            0 |       188.28 |
|    20200616 |  50.00 |              795.00 |           3017.04 |            0 |       238.28 |
|    20200619 |  50.00 |              845.00 |           2567.04 |            0 |       288.28 |
|    20200624 |  50.00 |              895.00 |             47.04 |            1 |        47.04 |
|    20200715 |  39.00 |              934.00 |             47.04 |            1 |        47.04 |
+-------------+--------+---------------------+-------------------+--------------+--------------+