algorithm creation

[Charlotte37]

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

[PaigeMiller]

---

@Charlotte37 wrote:

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'm sorry, I don't see how A has two insured persons.

[Charlotte37]

It's a mistake.

The A contract have 1 insured person valid whereas the others have 2 insured persons valid