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
 General SQL Server Forums
 New to SQL Server Programming
 Running Total

Author  Topic 

Vack
Aged Yak Warrior

530 Posts

Posted - 2009-09-23 : 14:06:56
Dealing with a table that holds General Ledger transactions. I want to create a view that gives me a running balance of each account. The code below Groups by year and month but the total I get is the amount just for that month.

datum = distribution date
reknr = Account number
bdr_hfl = amount


SELECT TOP (100) PERCENT YEAR(dbo.gbkmut.datum) AS 'Year', MONTH(dbo.gbkmut.datum) AS 'Month', dbo.gbkmut.reknr, SUM(dbo.gbkmut.bdr_hfl) AS Expr1,
dbo.grtbk.bal_vw
FROM dbo.gbkmut LEFT OUTER JOIN
dbo.grtbk ON dbo.gbkmut.reknr = dbo.grtbk.reknr
WHERE (dbo.gbkmut.transtype <> 'V')
GROUP BY YEAR(dbo.gbkmut.datum), MONTH(dbo.gbkmut.datum), dbo.gbkmut.reknr, dbo.grtbk.bal_vw
ORDER BY 'Year', 'Month'


Here is an example of what I get:

Year Month reknr Expr1 bal_vw
2009 7 13004 64174.34 B


The amount I get above is just the amount for July of 2009. What I'm looking for is July of 2009 and everything before that date.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-23 : 14:43:51
Are you using MS Sql Server 2005 (or later) ?

Be One with the Optimizer
TG
Go to Top of Page

Vack
Aged Yak Warrior

530 Posts

Posted - 2009-09-23 : 14:54:41
SQL Server 2005
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-23 : 14:59:10
ok - then try this:

select year(dateadd(month, datediff(month, 0, t.datum), 0)) as [year]
,month(dateadd(month, datediff(month, 0, t.datum), 0)) as [month]
,t.reknr
,k.bal_vw
,min(ca.expr1) as expr1
from dbo.gbkmut t
LEFT OUTER JOIN dbo.grtbk k ON t.reknr = k.reknr
cross apply (
select sum(t1.bdr_hfl) as expr1
from dbo.gbkmut t1
LEFT OUTER JOIN dbo.grtbk k1 ON t1.reknr = k1.reknr
where t1.reknr = t.reknr
and isNull(k1.bal_vw, '_') = isNull(k.bal_vw, '_')
and datediff(month, t1.datum, dateadd(month, datediff(month, 0, t.datum), 0)) >= 0
) ca
group by dateadd(month, datediff(month, 0, t.datum), 0)
,t.reknr
,k.bal_vw


woops - fixed the cross apply aliases in JOIN criteria

Be One with the Optimizer
TG
Go to Top of Page

Vack
Aged Yak Warrior

530 Posts

Posted - 2009-09-23 : 15:05:41
received a message cross apply not supported. I'm putting this code in view if that makes a difference.
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-23 : 15:07:39
shouldn't make a difference. Check the database compatibility level for that db. If it was a restored db from 2000 (or ealier) then you would need to set it to 90.

Be One with the Optimizer
TG
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-23 : 15:16:41
hmmm - actually database compatibiltiy doesn't make a difference for cross apply as long as the server is 2005.

Are running the create view statement from a Management Studio Query Window ?

Be One with the Optimizer
TG
Go to Top of Page
   

- Advertisement -