1

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.

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
  • What database? If SQL Server 2005+ or Oracle 9i+, a recursive query will create a list of numbers - otherwise you have to create a table (or temp table) yourself & populate it & then LEFT JOIN onto the GL table... – OMG Ponies Oct 28 '09 at 19:24
  • It's a SQL Server 2005 DB, and I'm trying to do the data access/etc through Access so I can just pop together a quick easy report. –  Oct 28 '09 at 19:27
  • Is there an accounts table where all the account numbers are stored? And is that all that's in that table are those three columns? – VinPepe Oct 28 '09 at 19:32
  • There's a table that has each acct number, it's description, and some other miscellaneous data. And no that's not all that's in the GLBalances table, there's cmpny_cd, acct_no, post_yr, post_prd, post_trn_amt, time_stamp. In the other table that I mentioned at the start of this comment, there's cmpny_cd, acct_no, acct_desc, and then about 15-20 fields that appear to be flags and id's for other table relations. I'm not *super* familiar with the software that creates the tables so I don't rightly know what all the other fields are. –  Oct 28 '09 at 19:40

1 Answers1

6

You can create a sheet of valid accounts and months with cross join. Look for the corresponding "real" row with a left join, and you're set:

;with months as
(
    select 0 as Month
    union all
    select Month + 1 from months where Month < 12
)
select a.acct_no, m.month as post_prd, IsNull(g.post_trn_amt,0)
from months m
cross join (select  distinct acct_no from @GLBalances) a
left join @GLBalances g 
    on m.month = g.post_prd 
    and a.acct_no = g.acct_no
order by a.acct_no, m.month

The "with months as" construct is a fancy way to create a table containing numbers 0 to 12. You can also create a real table containing those numbers, and do away with the "recursive common table expression" construct.

Here's the test data I used:

declare @GLBalances table (acct_no int, post_prd int, post_trn_amt int)
insert into @GLBalances
select 1011,0,-15000
union all select 1011, 1, 5000
union all select 1011, 2, -6000
union all select 1011, 4, 8000
union all select 1020, 5, 100
union all select 1020, 12, 300
union all select 1011, 9, 500
union all select 1011, 8, 0
Andomar
  • 232,371
  • 49
  • 380
  • 404
  • +1. Good concise answer with workable code. Pretty much the same code as in my second link, but already modified for @Josh's needs. Again, good answer. – David Oct 28 '09 at 19:59