0

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

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

Your Answer

Eduardo Almeida is a new contributor. Be nice, and check out our Code of Conduct.

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.