I have one lifetable with 121 rows and 3 columns (age, male and female). I have to fetch all the 121 rows considering the gender. If the gender is "Male" I fetch the column "Male" from lifetable as qx and the column "Female" as qy and vice-versa.
However, I need to calculate a fourth column, vx, whose value starts with 1 and the subsequent values are calculated as follows, using the previous value of vx itself:
vx = (vx / (1 - @interestRate)^age)
The age always goes from 0 to 120, incremented by one each step.
I am not well versed with CTEs, therefore, I'm taking baby steps to accomplish my tasks with this new method to me. and I need help.
When I try to calculate vx, the query returns 7381 instead of 121.
Here is my CTE:
DECLARE @gender VARCHAR(10) = 'MALE' DECLARE @interestRate NUMERIC(7, 5) = 0.0354 ;WITH cte AS ( SELECT lt.Age, CASE WHEN @gender = 'MALE' THEN lt.Male ELSE lt.Female END AS qx, CASE WHEN @gender = 'MALE' THEN lt.Female ELSE lt.Male END AS qy, CAST(1.0 AS NUMERIC(7, 5)) AS vx FROM LifeTable AS lt UNION ALL SELECT cte.Age + 1, cte.qx, cte.qy, CAST((vx / (1 + @interestRate)) AS NUMERIC(7, 5)) AS vx FROM cte WHERE cte.Age < 120 ) SELECT COUNT(*) AS NumberOfRows FROM cte OPTION (MAXRECURSION 0);
I am a little bit tired for I spent my afternoon trying to solve this, it's already night, so my question might be taken as low effort, but I really need your help to see where I am making mistakes.