Running Total with Filter Help

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

Transact-SQL (2012)

Running Total with Filter Help

Author

Topic

cardnal0602
Starting Member

11 Posts

Posted - 2014-06-24 : 13:10:04

Hey

I'm trying to get running totals by date while using a filter column to determine if I should add or subtract the current row with the previous row. I am not sure how to approach this without using a cursor.

The data looks like this:


tID	gID	    tran_date	balop	amount
3272	5841816	    12/14/2013	1	67
3281	5841816	    12/14/2013	0	0
3296	5841816	    12/28/2013	1	49.37
3301	5841816	    12/28/2013	-1	100
3402	5841816	    12/29/2013	1	6.06
3409	5841816	    1/13/2014	1	56.42
3459	5841816	    1/13/2014	0	0
3488	5841816	    1/16/2014	1	10.58
3516	5841816	    1/16/2014	-1	50

I need to use the "balop" filter to determine how to determine what the running total is on each row

Rules:


when balop =  0 then "total" = carry forward the prev. row amount 
when balop =  1 then "total" = prev. row amount + current row amount
when balop = -1 then "total" = current row amount - prev. row amount

The the output should end up like this:


tID	gID	    tran_date	balop	amount	Total
3272	5841816	    12/14/2013	1	67	67        *1st transaction row
3281	5841816	    12/14/2013	0	0	67        *prev. row amount
3296	5841816	    12/28/2013	1	49.37	116.37    *prev. row amount + current row amount
3301	5841816	    12/28/2013	-1	100	16.37     *current row amount - prev. row amount
3402	5841816	    12/29/2013	1	6.06	22.43     *prev. row amount + current row amount
3409	5841816	    1/13/2014	1	56.42	78.85     *prev. row amount + current row amount
3459	5841816	    1/13/2014	0	0	78.85     *prev. row amount
3488	5841816	    1/16/2014	1	10.58	89.43     *prev. row amount + current row amount
3516	5841816	    1/16/2014	-1	50	39.43     *current row amount - prev. row amount

So if I wanted to know the running total on 12/28/2013, I would like to return 16.37 (because tID = 3301 is the last transactionID on that day)

What would be the best way to attack this?

Thanks

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-06-24 : 15:00:57

Id suggest using SUM and an OVER clause. you'll need to use a CASE expression to calculate positive/negative numbers, but something like this should get you going:
http://ole.michelsen.dk/blog/calculate-a-running-total-in-sql-server-2012/

cardnal0602
Starting Member

11 Posts

Posted - 2014-06-24 : 16:37:27

Thanks!

quote:
Originally posted by Lamprey

Id suggest using SUM and an OVER clause. you'll need to use a CASE expression to calculate positive/negative numbers, but something like this should get you going:
http://ole.michelsen.dk/blog/calculate-a-running-total-in-sql-server-2012/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources