I'm trying to get a report built up from data mining our accounting software.
We have a table that stores the balances of each account in a general ledger for a given period (which is 0-12, 0 being carry over from last year, 1-12 being the corresponding month), the amount, and other data I don't need.
I'm trying unsuccessfully to get a value for each account for each month, however there isn't always a corresponding entry. I've tried left outer joins, cross joins, inner joins, and can't seem to get it to work how I want. I've even tried doing left outer joins with a table containing 'Initial' as item 0 and 12 other entries, one name for each month.
Here's a sample of the data:
GLBalances table:
acct_no | post_prd | post_trn_amt
1011 | 0 | -15000
1011 | 1 | 5000
1011 | 2 | -6000
1011 | 4 | 8000
1020 | 5 | 100
1020 | 12 | 300
1011 | 9 | 500
1011 | 8 | 0
etc...
What I'd like to get out is:
acct_no | post_prd | post_trn_amt
1011 | 0 | -15000
1011 | 1 | 5000
1011 | 2 | -6000
1011 | 3 | 0
1011 | 4 | 8000
1011 | 5 | 0
1011 | 6 | 0
1011 | 7 | 0
1011 | 8 | 0
1011 | 9 | 500
1011 | 10 | 0
1011 | 11 | 0
1011 | 12 | 0
1020 | 0 | 0
1020 | 1 | 0
1020 | 2 | 0
1020 | 3 | 0
1020 | 4 | 0
1020 | 5 | 100
1020 | 6 | 0
1020 | 7 | 0
1020 | 8 | 0
1020 | 9 | 0
1020 | 10 | 0
1020 | 11 | 0
1020 | 12 | 300
etc...
So basically 13 entries for each acct for a particular year even if there's no entry for that period.
I'm sure this is way easier than I'm making it, I'm just struggling since I don't deal with SQL on a daily basis. Any help would be much appreciated.