My current issue is that I have a running balance, where one value falls below another the running balance needs to reset. But not only reset, but also use a another value as its starting value and start the balance again.
Below is the table with data in it:
+-------------+--------+---------------------+-------------------+--------------+-------------+
| 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 |
+-------------+--------+---------------------+-------------------+--------------+-------------+
Actual Calc is the desired outcome and Running account fees is the issue.
Running account fees is the running balance of "Amount" and overlimit_balance is the test. We need to see that the running_accountfees isn't greater than over limit,
If it is, take overlimits value and start calculating again by adding amount on again.
My query that produced this:
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