I have a table for which I need a “special” running total. More specifically there should be a close communication between the credit and the debit column. The scenario should be in the following order
|
“Credit” |
“Debit” |
“Balance credit” |
“Balance debit” |
|
6 |
0 |
6 |
0 |
|
5 |
0 |
11 |
0 |
|
0 |
4 |
7 |
0 |
|
0 |
9 |
0 |
2 |
|
3 |
0 |
1 |
0 |
|
0 |
5 |
0 |
4 |
|
0 |
2 |
0 |
6 |
I have to point out that this is done in a grouped form, where the CustormerID is the grouped clause. Now I have achieved this to a point where the calculations work only if the credit column is bigger than 0, but when the account starts with debit I get only sums of the column not the needed subtractions. What am I missing? Thanks in advance.
DECLARE @PrevRunBal MONEY --Overall running total SET @PrevRunBal = 0DECLARE @PrevGrpBal MONEY --Running total resets when account changes SET @PrevGrpBal = 0 DECLARE @PrevRunCnt INT --Overall running count (ordinal rank) SET @PrevRunCnt = 0 DECLARE @PrevGrpCnt INT --Running count resets when account changes SET @PrevGrpCnt = 0 DECLARE @PrevAcctID INT --The "anchor" and "account change detector" SET @PrevAcctID = 0changes SET @PrevGrpBalP = 0 update Temp SET --===== Running Total @PrevRunBal = RunBal = @PrevRunBal + dolguva, -- @iznos =RunBal= dolguva - pobaruva + @sdol -@spob, --===== Grouped Running Total (Reset when account changes) @PrevGrpBal = Bcredit = CASE WHEN CustomerID = @PrevAcctID THEN CASE WHEN CREDIt > DEBIT or CREDIT = 0 THEN (@PrevGrpBal+CREDIT)-(DEBIT) WHEN DEBIT>CREDIT or DEBIT =0 THEN (@PrevGrpBal+DEBIT)-DEBIt END ELSE CASE WHEN CREDIT >DEBIT THEN (CREDIT) WHEN DEBIT > CREDIT THEN DEBIT -- restarts from 0 if only 1 rec. END END, --===== Running Count (Ordinal Rank) @PrevRunCnt = RunCnt = @PrevRunCnt + 1, --===== Grouped Running Total (Ordinal Rank, Reset when account changes) @PrevGrpCnt = GrpCnt = CASE WHEN CustomerID = @PrevAcctID THEN @PrevGrpCnt + 1 ELSE 1 -- Restarts count at "1" END, --===== "Anchor" and provides for "account change detection"
@PrevAcctID = CustomerID