Hello,
I have a table with the history of insured persons by contract.
For example, the A contract have 2 insured persons valid.
| CONTRACT_NUMBER | RANK | NAME_RANK | STATUS | SUBSCRIPTION_DATE | CANCELLATION_DATE | BIRTHDAY_MAIN_INSURED |
| A | 1 | main insured | removed | 01/10/2005 | 20/01/2007 | 01/05/1976 |
| B | 1 | main insured | valid | 01/01/2019 | | 15/09/1955 |
| B | 2 | spouse | valid | 01/06/2019 | | 15/09/1955 |
| C | 1 | main insured | valid | 01/01/2019 | | 04/07/1982 |
| C | 2 | spouse | valid | 01/01/2019 | | 04/07/1982 |
| D | 1 | main insured | valid | 01/03/2009 | | 02/08/1944 |
| D | 2 | spouse | removed | 01/05/2010 | 15/09/2018 | 02/08/1944 |
I have a 2nde table with the amount of contributions :
| Age | Individual_Membership | Collective_Membership |
| Until 29 years old | 8 | 44,8 |
| From 30 to 65 years old included | 22,4 | 44,8 |
| From 66 to 70 years old included | 27,2 | 54,4 |
| From 70 | 34 | 68 |
I want to know the amount of the contributions of each contract for each year.
If a contract has only one insured then the amount is in the column "Individual_Membership" otherwise in the column "Collective_Membership".
if the contract starts in "individually" in the year and ends in "collective" then it is necessary to prorate the amounts.
The transition to the senior slice is 01/01 of the following year
My final table :
| CONTRACT_NUMBER | YEAR | AMOUNT |
| A | 2005 | 2,02 |
| A | 2006 | 8 |
| A | 2007 | 1,23 |
| B | 2019 | 9,23 + 26,27 = 35,5 |
| C | 2019 | 44,8 |
| D | 2009 | 18,78 |
| D | 2010 | 8,94 + 36,52 = 45,46 |
| D | 2011 | 54,4 |
| D | 2012 | 54,4 |
| D | 2013 | 54,4 |
| D | 2014 | 54,4 |
| D | 2015 | 68 |
| D | 2016 | 68 |
| D | 2017 | 68 |
| D | 2018 | 48,07 + 9,97 = 58,04 |
| D | 2019 | 34 |
Thank you very much for your help
