Forum Discussion

mrleijzer's avatar
mrleijzer
Helper I
3 years ago
Solved

Indexing yearly subscriptions

Hello everyone,

 

I have a pretty hard to explain situation which I'd like to solve, I'll give it a go:

I have 3 tables:

  1. customer information (ID, name, address, etc.)
  2. yearly maintenance work for this customer, these are contracts for 15 years so this table has 15 rows for each customer (ID from customer, year, costs of the year)
  3. an index table to calculate the yearly index on the yearly subscription for the customer (year, index %, year is related to the startyear + 1)

So let's say I have a customer and to keep it simple this example has only 3 years of subscription.

NameYearCosts
Customer 120214000
Customer 120221000
Customer 120231000

 

So the total costs are 7000, the customer will pay a yearly amount in a spread of the 3 years: 6000 / 3 = 2000 (index excluded)

 

And let's say my index table looks like this

YearIndex
20202%
20213%
20223%
20234%

 

Now, the first year has no index, so indexing the subscription only starts from the second year.

 

How can I produce a table calculating the amount of money that must be paid by the customer, index included?

 

It would look like this (the amount is calculated from the amount of last year + index):

 

NameYearAmountIndex
Customer 120212000,000%
Customer 120222060,003%
Customer 120232142,404%

 

I hope I've explained clear enough, thanks in advance for anyone's time and effort.