Running Total Problem - SQL Server Forums

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)

Running Total Problem

Author

Topic

Vack
Aged Yak Warrior

530 Posts

Posted - 2009-09-29 : 14:58:28

Have a Calendar Table called Calendar. This table has a dt field that has a record for every date from 01/01/1998 to 12/31/2030.

I have a 2nd table grtbk which holds account numbers.

I created a view which cross joins these two tables. So every account is attached to every date. I used the following to create that view called accountdatePL


SELECT     dbo.Calendar.dt, dbo.grtbk.reknr
FROM         dbo.Calendar CROSS JOIN
                      dbo.grtbk
WHERE     (dbo.grtbk.bal_vw = 'W')

Now I have a third table that holds transaction information for the account numbers. I want to sum the amounts of those transactions and group them into Month and Year. If an account does not have any transactions for a period (or no records in the gbkmut), I want the amount to show zero. I tried the following Code but it still only shows accounts that have actual totals and not zero.

I think I've tried a dozen different ways and have had no luck. Any suggestions would be greatly appreciated.


select accountDatePL.reknr, ISNULL(SUM(dbo.gbkmut.bdr_hfl), 0) AS Amount,YEAR(dbo.gbkmut.datum) AS Year, 
                      MONTH(dbo.gbkmut.datum) AS Month
from AccountDatePL left outer join gbkmut on accountdatePL.reknr = gbkmut.reknr and gbkmut.transtype <>'V'
group by accountdatePL.reknr,YEAR(dbo.gbkmut.datum),MONTH(dbo.gbkmut.datum)
order by 'Year',month

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-29 : 15:31:14

Your topic description says "running total problem" but your code doesn't have a running total and your question is just about returning rows for dates that aren't in your grtbk table. So that is confusing to me this should solve the missing rows issue. You were SELECTing and GROUPing BY the gbkmut dates instead of your accountDatePL dates. If this doesn't work then please post sample data and expected results like I provided with your last, similar issue: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=133545


select ad.reknr
       ,ISNULL(SUM(g.bdr_hfl), 0) AS Amount
       ,YEAR(ad.dt) AS Year
       ,MONTH(ad.dt) AS Month
from   AccountDatePL ad
left outer join gbkmut g
       on ad.reknr = g.reknr 
       and datediff(day, ad.dt, g.datum) = 0
       and g.transtype <>'V'
group by ad.reknr
       ,YEAR(ad.dt)
       ,MONTH(ad.dt)
order by 'Year',month

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources