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.
| Author |
Topic |
|
Vack
Aged Yak Warrior
530 Posts |
 Posted - 2009-09-30 : 12:56:04
|
I've been working on this issue for a while and may be going at it the wrong way. Hopefully I can get led in the right direction by one of the experts. I am dealing with two tables:grtbk (Account Number Listing)gbkmut (General Ledger Transactions)My ultimate goal is to generate a Profit and Loss Statement. But I need to get MTD and YTD totals calculated before I pull the values onto my report. Relevant fields in grtbk.reknr (account number)bal_vw (Code which specifies P&L account='W')Relevant fields in gbkmut.reknr (account Number)datum (Trx Date mm/dd/yyyy)bdr_hfl (Trx Amount)transtype(want to exclude voided trx's where transtype='V')What I need are trx's from the gbkmut grouped by reknr, month, and year. I need my date to look like this.reknr Amount month Year Amt_YTD1001 $300 1 2009 $3001001 $450 2 2009 $7501001 $599 3 2009 $13491002 $200 1 2009 $2001002 $0.00 2 2009 $2001002 $600 3 2009 $800 On my P&L if I have a YTD amount I want the account to print. Even though I may have 0.00 for the MTD. So if there are no records in the gbkmut for a period but if there are for the year I need it to show zero for the amount. I do not need to show the account if the YTD and MTD are both zero. My original thought was to create some sort of SQL view to get the data I need but had issues getting the account to show if there were no transactions for a period. Another thing I tried was creating a calendar table which crossed joined to the account table giving me a account number attached to every date. That seemed to give me the result I was looking for except for it takes way too long to run. So I am looking for any other ideas in how I may be able to tackle this issue. I have to believe this is possible. |
|
|
|
|
|
Linking Question/Report design issue