# CTE SQLFiddle

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.

Your problem is the anchor in the CTE returns all 120 rows instead of a single anchor row. Try this code instead, notice the WHERE clause in the CTE SELECT:

``````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
WHERE Age = 1
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);
``````
• You are right! Thank you very, @HandyD! Mar 26, 2020 at 13:41
• It should be 1 - @interestRate Mar 27, 2020 at 12:33