Need help to display dynamic running balance

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)

Need help to display dynamic running balance

Author

Topic

gena_mak
Starting Member

26 Posts

Posted - 2013-01-24 : 16:08:08

Hi
I need help with query that would display monthly report for a store with dynamic running balance adding up for every month.
SQL 2005

Here is the query


CREATE TABLE #temp (storeid int, debit float, credit float, calmonth int, calyear int)

insert into #temp (storeid, debit, credit, calmonth, calyear)
values (12, 500, 900, 1, 2013)

insert into #temp (storeid, debit, credit, calmonth, calyear)
values (12, 400, 400, 12, 2012)

insert into #temp (storeid, debit, credit, calmonth, calyear)
values (12, 800, 100, 11, 2012)

insert into #temp (storeid, debit, credit, calmonth, calyear)
values (12, 100, 100, 10, 2012)

insert into #temp (storeid, debit, credit, calmonth, calyear)
values (12, 200, 100, 9, 2012)

insert into #temp (storeid, debit, credit, calmonth, calyear)
values (10, 500, 700, 1, 2013)

insert into #temp (storeid, debit, credit, calmonth, calyear)
values (10, 800, 600, 12, 2012)

select storeid, debit, credit, calmonth, calyear, balance = credit - debit
from #temp
where storeid = 12
order by calyear desc, calmonth desc

DROP TABLE #temp

This is what I need the last column to add up to most recent month unless up to month and year specified in the condition


storeid     debit                  credit                 calmonth    calyear     balance     running_balance
----------- ---------------------- ---------------------- ----------- ----------- ----------------------
12          500                    900                    1           2013        400            -400
12          400                    400                    12          2012        0              -800
12          800                    100                    11          2012        -700           -800
12          100                    100                    10          2012        0              -100
12          200                    100                    9           2012        -100           -100

Any help greatly apprciated
Thank you,
Gena

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-24 : 16:41:12

SQL 2005 is very ill-suited for doing running totals type of calculations, so the following would work, but the performance is likely to be blah. If you want to exclude the current month, instead of b.calmonth <= t.calmonth use b.calmonth < t.calmonth

SELECT
	t.*,
	s.running_balance
FROM
	#temp t
	CROSS APPLY
	(
		SELECT SUM(b.credit - b.debit) running_balance
		FROM #temp b
		WHERE b.calyear <= t.calyear AND b.calmonth <= t.calmonth
		AND b.storeid=t.storeid
	) s
ORDER BY calyear desc, calmonth DESC

gena_mak
Starting Member

26 Posts

Posted - 2013-01-25 : 08:46:47

Thanks, James
Is there something better for SQL2008? I have two production servers and one of them runs 2008.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-25 : 09:07:52

There are no additional features in SQL 2008 - it is the same as 2005 - with regards to time series/running total type of calculations. SQL 2012 is a whole different story - it has enhanced windowing functions which makes doing such calculations a breeze - simple syntax and very efficient processing.

gena_mak
Starting Member

26 Posts

Posted - 2013-01-25 : 09:43:17

James, there is something not working. I plugged in some numbers and they do not add up in running total.


CREATE TABLE #temp (storeid int, debit float, credit float, calmonth int, calyear int)
SET NOCOUNT ON
insert into #temp (storeid, debit, credit, calmonth, calyear)
values (12, 13588.97, 14000.00, 1, 2013)

insert into #temp (storeid, debit, credit, calmonth, calyear)
values (12, 12848.42, 12948.42, 12, 2012)

insert into #temp (storeid, debit, credit, calmonth, calyear)
values (12, 75662.27, 75662.27, 10, 2012)

insert into #temp (storeid, debit, credit, calmonth, calyear)
values (12, 36379.57, 36379.57, 9, 2012)

insert into #temp (storeid, debit, credit, calmonth, calyear)
values (10, 500, 700, 1, 2013)

insert into #temp (storeid, debit, credit, calmonth, calyear)
values (10, 800, 600, 12, 2012)

SET NOCOUNT OFF

select storeid, debit, credit, balance = credit - debit, calmonth, calyear
from #temp
where storeid = 12

SELECT
	t.*,
	record_balance = credit - debit, s.running_balance
FROM
	#temp t
	CROSS APPLY
	(
		SELECT SUM(b.credit - b.debit) running_balance
		FROM #temp b
		WHERE b.calyear <= t.calyear AND b.calmonth <= t.calmonth
		AND b.storeid=t.storeid
	) s
	where t.storeid = 12
ORDER BY calyear desc, calmonth DESC

DROP TABLE #temp

The result looks like that


storeid     debit                  credit                 calmonth    calyear     record_balance        running_balance
----------- ---------------------- ---------------------- ----------- ----------- ---------------------- ----------------------
12          13588.97               14000                  1           2013        411.030000000001       411.030000000001
12          12848.42               12948.42               12          2012        100                    100
12          75662.27               75662.27               10          2012        0                      0
12          36379.57               36379.57               9           2012        0                      0

And must be this, last column must add up numbers.


storeid     debit                  credit                 calmonth    calyear     record_balance        running_balance
----------- ---------------------- ---------------------- ----------- ----------- ---------------------- ----------------------
12          13588.97               14000                  1           2013        411.030000000001       511.030000000001
12          12848.42               12948.42               12          2012        100                    100
12          75662.27               75662.27               10          2012        0                      0
12          36379.57               36379.57               9           2012        0                      0

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-25 : 10:46:50

I messed up gena - sorry about that. Change the inner where clause to this:

....
		FROM #temp b
		WHERE b.calyear*100+b.calmonth <=t.calyear*100+t.calmonth
		AND b.storeid=t.storeid
....

gena_mak
Starting Member

26 Posts

Posted - 2013-01-25 : 11:30:02

Perfect, thank you James.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-25 : 14:50:46

You are welcome - glad to be of help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources