SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Need help to display dynamic running balance
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

gena_mak
Starting Member

26 Posts

Posted - 01/24/2013 :  16:08:08  Show Profile  Reply with Quote
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

Edited by - gena_mak on 01/24/2013 16:12:06

James K
Flowing Fount of Yak Knowledge

3759 Posts

Posted - 01/24/2013 :  16:41:12  Show Profile  Reply with Quote
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 - 01/25/2013 :  08:46:47  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3759 Posts

Posted - 01/25/2013 :  09:07:52  Show Profile  Reply with Quote
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 - 01/25/2013 :  09:43:17  Show Profile  Reply with Quote
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

Edited by - gena_mak on 01/25/2013 09:45:35
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3759 Posts

Posted - 01/25/2013 :  10:46:50  Show Profile  Reply with Quote
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 - 01/25/2013 :  11:30:02  Show Profile  Reply with Quote
Perfect, thank you James.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3759 Posts

Posted - 01/25/2013 :  14:50:46  Show Profile  Reply with Quote
You are welcome - glad to be of help.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000