Re: SQL build file of dates

Bruce,

Perhaps a liilte late in this thread.

If you have to calculate cummulative data for the last 24 months each
month, can you not better calculate the cumulative totals per month and add
that to a summary PF.

Use that summary PF to generate the report.

Crunching data over two years every month seems to me a resource hungry
query. Not found figures in a month can be dealt with using COALESCE/VALUE
to substitute the nulls.

Just my thoughts.

Regards,
Carel Teijgeler

P.S.
To all on the list: Happy New Year.



*********** REPLY SEPARATOR  ***********

On 29-12-2006 at 9:46 Bruce Guetzkow wrote:

Paul:

What I have is a month-end report that I need to generate for one of our

clients.  They want a rolling-24-month history of accounts listed here,

including accounts listed, amount collected, our commission, by month.  Of

course, there aren't always guaranteed figures for any column for a given

month.  So, I have been manually updating a file to contain the list of

months to produce the report for.  This way I can use the "months" file as

the primary file in a join, all other files connected via a left join.  In

this way I am guaranteed a record for each month, even if there is no
actual

data for that month.  We don't want the report to show Jan/Feb/Mar/May,

skipping April...just show April with $0.00 in whatever fields.  The report

is actually a file, downloaded as an XLS spreadsheet.

So, I'm trying to find an easy way to generate the "months" file on the

fly, given a start or end date.  If SQL isn't the best tool for the job,
that's

fine...I'm just trying to see if there is a reasonable way to use it for

this.

I'm going to check out Rob's suggestion to see if that will fly.  If not,

I already have a Plan B ready.  Thanks for any help you can offer, even if

that help is "use the right tool!"