| 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.calmonthSELECT
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. |
|
|
|
|
|
|
Need help to display dynamic running balance