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:
- customer information (ID, name, address, etc.)
- 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)
- 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.
| Name | Year | Costs |
| Customer 1 | 2021 | 4000 |
| Customer 1 | 2022 | 1000 |
| Customer 1 | 2023 | 1000 |
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
| Year | Index |
| 2020 | 2% |
| 2021 | 3% |
| 2022 | 3% |
| 2023 | 4% |
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):
| Name | Year | Amount | Index |
| Customer 1 | 2021 | 2000,00 | 0% |
| Customer 1 | 2022 | 2060,00 | 3% |
| Customer 1 | 2023 | 2142,40 | 4% |
I hope I've explained clear enough, thanks in advance for anyone's time and effort.
