# 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.

New contributor
Eduardo Almeida is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.

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! – Eduardo Almeida 2 days ago
• It should be 1 - @interestRate – KumarHarsh yesterday