SQL running totals and subtractions

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SQL running totals and subtractions

Author

Topic

sterjo
Starting Member

1 Post

Posted - 2008-04-08 : 18:20:15

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 = 0
DECLARE @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 = 0
changes
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
Apologies if I'm posting in a wrong forum

Nik

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-09 : 11:46:21

Could you post your table structures?

cvipin
Yak Posting Veteran

51 Posts

Posted - 2008-04-09 : 13:43:39

sterjo,

This should solve your problem. Let me know if this helps.

create table #temp (dr decimal(18,2), cr decimal(18,2), rtDr decimal(18,2), rtCr decimal(18,2))
insert into #temp values (6, 0, 0, 0)
insert into #temp values (5, 0, 0, 0)
insert into #temp values (0, 4, 0, 0)
insert into #temp values (0, 9, 0, 0)
insert into #temp values (3, 0, 0, 0)
insert into #temp values (0, 5, 0, 0)
insert into #temp values (0, 2, 0, 0)

declare @Diff decimal(18,2)
set @Diff = 0

update #temp
set @Diff = rtDr = (@Diff + dr -cr)

update #temp set rtDr = CASE WHEN rtDr < 0 THEN 0 ELSE rtDr END,
rtCr = CASE WHEN rtDr < 0 THEN ABS(rtDr) ELSE 0 END
select * from #temp

DROP table #temp

Thanks
Vipin

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources