Finding the Running Total - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Finding the Running Total

Author

Topic

Aiby
Yak Posting Veteran

71 Posts

Posted - 2007-11-04 : 10:35:26

/*
To find the running total, this tip i have got from this Forum itself..
But here i have a situation.. The out put tables recordset is Union of two or more conditions..
When Updateing the Running total field, I want to keep an Order of TransactionID!!!
Can i manage it in the same #Temp table.. some how.. or is there any other solution some one can suggest for this purpose

*/

Declare @mAccountHeadID Int ,
@RunningTotal Float

SET @mAccountHeadID =994
SET @RunningTotal =0

DROP TABLE #Temp
BEGIN

SELECT A.intTransactionID ,
A.fltAmount ,
@RunningTotal as RunningTotal
INTO #Temp
FROM
(
SELECT
dbo.faTransactionChild.intTransactionID ,
dbo.faTransactionChild.fltAmount

FROM dbo.faTransactionChild
WHERE (dbo.faTransactionChild.intAccountHeadID = @mAccountHeadID AND
dbo.faTransactionChild.intSerialNo <> 1
)
UNION ALL

SELECT
dbo.faTransactionChild.intTransactionID ,
dbo.faTransactionChild.fltAmount
FROM dbo.faTransactionChild
WHERE (dbo.faTransactionChild.intByAccountHeadID = @mAccountHeadID)
) A

UPDATE #Temp SET @RunningTotal = RunningTotal = @RunningTotal + fltAmount
END
SELECT * FROM #Temp

<----------OUT PUT ------------------>
10 10000009.0 10000009.0
11 500000.45000000001 10500009.449999999
7 10000009.0 20500018.449999999
8 500000.45000000001 21000018.899999999
5 10000009.0 31000027.899999999
6 500000.45000000001 31500028.349999998
9 10000009.0 41500037.349999994
12 500000.45000000001 42000037.799999997
16 10000009.0 52000046.799999997
15 500000.45000000001 52500047.25
14 10000009.0 62500056.25
13 500000.45000000001 63000056.700000003
4 10000009.0 73000065.700000003
20 500000.45000000001 73500066.150000006
19 10000009.0 83500075.150000006
The running total should be updated in the field by keeping Transaction ID in Order other!!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2007-11-05 : 23:04:34

One problem with that technique (@variable = column = expression ) to update a table with a running total is that the updates don't happen in any specific order.
One way to solve that is to pre-create #temp with a clustered primary key on the column you want to order the total by. The updates will more likely occur in that order:


if object_id('tempdb..#temp') > 0 drop table #temp

create table #temp 
	(intTransactionID int primary key clustered
	,fltAmount float
	,RunningTotal float)

--Your sample data
insert #temp (intTransactionID, fltAmount, RunningTotal)
select intTransactionID, fltAmount, RunningTotal
from	(
	select 10 intTransactionID,  10000009.0 fltAmount, 0 RunningTotal union all
	select 11, 500000.45000000001, 0 union all
	select 7, 10000009.0, 0 union all
	select 8, 500000.45000000001, 0 union all
	select 5, 10000009.0, 0 union all
	select 6, 500000.45000000001, 0 union all
	select 9, 10000009.0, 0 union all
	select 12, 500000.45000000001, 0 union all
	select 16, 10000009.0, 0 union all
	select 15, 500000.45000000001, 0 union all
	select 14, 10000009.0, 0 union all
	select 13, 500000.45000000001, 0 union all
	select 4, 10000009.0, 0 union all
	select 20, 500000.45000000001, 0 union all
	select 19, 10000009.0, 0
	) a

declare @RunningTotal float
set @RunningTotal = 0
UPDATE #Temp SET @RunningTotal = RunningTotal = @RunningTotal + fltAmount

select * from #temp order by 1

OUTPUT:
intTransactionID fltAmount                                             RunningTotal                                          
---------------- ----------------------------------------------------- ----------------------------------------------------- 
4                10000009.0                                            10000009.0
5                10000009.0                                            20000018.0
6                500000.45000000001                                    20500018.449999999
7                10000009.0                                            30500027.449999999
8                500000.45000000001                                    31000027.899999999
9                10000009.0                                            41000036.899999999
10               10000009.0                                            51000045.899999999
11               500000.45000000001                                    51500046.350000001
12               500000.45000000001                                    52000046.800000004
13               500000.45000000001                                    52500047.250000007
14               10000009.0                                            62500056.250000007
15               500000.45000000001                                    63000056.70000001
16               10000009.0                                            73000065.700000018
19               10000009.0                                            83000074.700000018
20               500000.45000000001                                    83500075.150000021

Be One with the Optimizer
TG

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-06 : 01:48:57

Where do you want to show data?
If you use front end application, you can easily do running total there. Note that Reports have Running Total feature

EDIT: TG nice to see you here after a long time

Madhivanan

Failing to plan is Planning to fail

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2007-11-06 : 07:04:25

quote:
EDIT: TG nice to see you here after a long time

Nice to be back! Thanks MadMan

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources