> 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...

More than likely, yes.

In our case, because we have to "normalize" dates to meet reporting and
financial periods, which aren't calendar month based, we have created a
DATES and MONTHS tables which relate a date to a given month and a month
to starting and ending dates.

> 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)

I would extend your SP to return the extracted Month/year.

A better SQL would be

select
DG.month,
DG.year,
sum(
(SELECT SUM( Transamt)
FROM tbl_transaction
WHERE
dtm_transdate = DG.date
)
from sp_dategen('01jan2009', '01may2009') DG
GROUP BY DG.year, DG.month

This allows for the "JOIN" to the transactions to be based on the date
field, which more than likely would have an index. You original SQL
would have generated 1 natural scan of the transaction table for each
date returned by the SP.


Sean