Calculate montly total using formula(loop)

[montrealdevils]

We sell service contracts with our product that vary in length of time of coverage. It is always sold in "monthly" increments. For example you can purchase a 12, 24, or 36 month contract, it can actually be any # of months it doesn't matter.

For forecasting I need to report how much of money we can defer each month over the life of the contract.

For example, we sell a 12 month contract for $12,000 on 8/1/04. I need the report to calculate 1/12 of $12,000 revenue for the next 12 months.

8/1/04 $1000
9/1/04 $1000
10/1/04 $1000
11/1/04 $1000
..... and so on until the number of months in the contract are complete.

The data is being pulled from our invoice table that gives me:

InvoiceNumber InvoiceDate Part # Qty Unit Price
235789 8/1/04 02-006-0 12 $1000

I'm hoping to be able to use a crosstab in the report footer to summarize the monthly totals by part #.

Thanks in advance for any advice you can provide!!

 

[montrealdevils]

I forgot, I'm using --

Crystal Rerports 9.0
with SQL 2000 db