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
Go to Top of Page

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.
Go to Top of Page

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.
Go to Top of Page

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
Go to Top of Page

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
....
Go to Top of Page

gena_mak
Starting Member

26 Posts

Posted - 2013-01-25 : 11:30:02
Perfect, thank you James.
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-25 : 14:50:46
You are welcome - glad to be of help.
Go to Top of Page
   

- Advertisement -