Hi all,

I'm summing financial transactions in a table, and would like to display months
where there are no transactions as zero balances.

Consider the table

Date Amount
----------------------
2jan2009 50.00
17jan2009 32.00
4mar2009 70.00
6apr2009 110.00


I'd love to then have a query that would present the following:
jan2009 82.00
feb2009 0.00 --- The zero balance
mar2009 70.00
apr2009 110.00

I'm really going to have to create a date source by incrementing months
and years on the left side of a left join, then sum against the transation
table on the right, aren't I...

Sort of like:

select DG.month, DG.year, sum(transamt) from sp_dategen('01jan2009',
'01may2009') DG
LEFT JOIN tbl_transaction
ON DG.month = extract(month from dtm_transdate)
AND DG.year = extract(year from dtm_transdate)


the sp_dategen function manufactures incrementing months and year values between
the supplied ranges.

Am I barking up the wrong tree? Is there an easier way?

Nige.



--
Nigel Weeks

Prism9 Technology
e: nweeks@...
m: 0408 133 738
w:http://prism9.com

fb:http://facebook.com/profile.php?id=604592926