price calculation

[hello]

Hi.
I know how to do the price calculation (invoice table ) for todays year.

But i would like to do the price calculation for every year in the past too, and then compsre it with what my customers did paid. So i will get a history.

I have a table calles invoice:
Name start.year end.year fee

It's a contract as you can see. The difficsualt part is that the fees/charges changes every year according to "consumer price index". they only pay once a year.

Since the dates in the invoice table is a range it is difficault to compare it to what have been paid.

How can i solve that?

Here is the calculation:

(this years cpi)*fee/(cpi for the start.year)

So if start year is 1995 and the fee is 500 and i want to see what thr customet paid 1998 I Have to calculate it like (start years cpi)*fee/(CPI for 1998)

I want a query which shows all the years 1980-2012 with what was inoiced that specific year and compare it with what was paid.

Thanks a lot

 

[Pat Hartman]

Create a table with the 12 years in it. Include that in the query. This will give you 12 rates for every customr by creating a Cartesian Product.

 

[hello]

Create a table with the 12 years in it. Include that in the query. This will give you 12 rates for every customr by creating a Cartesian Product.

Thanks!
I actually just did that. I inserted
2008
2009
2010
2011
2012
in another table.

Then everything looked perfect
I had every contract and calculated the invoice for the years above and compared it what they did pay that year.

The problem is now, for example:
If a contract is written 2010-2015, I will get it in 2008 and 2009 above.
I think you understand.
How can i solve that?

Thanks a lot

 

[Pat Hartman]

Filter the final results with a where clause that only includes rows for years within the term of the contract.