3

UPDATE #2

The answer can be found here: https://stackoverflow.com/questions/60766448/update-1-how-to-refer-to-the-previous-value-of-a-computed-column-in-sql-serve

I'll post as an answer here as well so I can close this question with an answer that hopefully can help others in the future.

I was lucky enough to find people willing to help me. Thank you @Squirrel and @Nick from StackOverflow!

I updated the fiddle with observations made by @Squirrel in the comments: http://sqlfiddle.com/#!18/e0ef2c/2

UPDATE #1

I was able to get some evolution in my case with a user defined scalar function:

create function [dbo].[tpx]
(
    @qx numeric(15, 10)
)
returns numeric(15, 10)
as
begin

    declare @tpx as numeric(15, 10) = 1;

    return @tpx * ( 1 - @qx );

end;

I was not able to update my fiddle for some reason, but my query is now like this:

use GESTAO_ATUARIAL;

select
case when row_number() over (order by id) = 1 then
    1
else
    lag(tpx, 1, 1) over (order by id) * (1 - lag(qx, 1) over (order by id))
end as tpx_final
from (
    select
        t.id,
        t.ben,
        t.mensalizacao,
        t.qx,
        (lag(dbo.tpx(qx), 1, 1) over (order by id)) as tpx,
        t.vx,
        t.valor_ben
    from (
        select
            aux.Id as id,
            dbo.CalcularIdade(aux.[DataNascimento], getdate()) + row_number() over (order by aux.Id) - 1 as idade,
            case when aux.Sexo = 'M' and aux.CondicaoParticipacao = 'N' then
                cast(mrt.Masculino as numeric(7,5))
            when aux.Sexo = 'M' and aux.CondicaoParticipacao = 'S' then
                inv.Masculino
            when aux.Sexo = 'F' and aux.CondicaoParticipacao = 'S' then
                inv.Feminino
            else
                mrt.Feminino
            end as qx,

            case when (row_number() over (order by aux.Id)) = 1 then
                1
            else
                (1 / power( 1 + .03370, row_number() over (order by aux.Id) - 1))
            end as vx,
            case when aux.Sexo = 'M' then
                men.FatorMensalizacaoMasculino
            else
                men.FatorMensalizacaoFeminino
            end as mensalizacao,
            cast(aux.ValorBeneficio * ( 1 + 0.03284) as numeric(10,2)) as valor_ben,
            aux.TipoBeneficio as ben

        from Mortalidade mrt
        left join AuxilioAlimentacaoParticipantes aux
        on mrt.Idade >= dbo.CalcularIdade(aux.[DataNascimento], getdate())

        left join MortalidadeInvalidos inv
        on mrt.Idade = inv.Idade

        left join FatorMensalizacao men
        on mrt.Idade = men.Idade

        where aux.Id = 1
    ) as t
) as s

But the results are still a little off, the first three rows are okay, but the following rows present completely wrong results.

At this point I do not really know if this is the correct approach to solve this problem in SQL Server.

Results:

enter image description here

What it should be:

enter image description here


ORIGINAL

I am trying to transfer my IAS 19 tool that calculates the obligations of the company with its employees' benefits from MS Excel to MS SQL Server.

Yesterday, I achieved significant progress, but when I thought I had succeeded completely, I've gotten stuck in a problem that needs a little more experience than what I have with SQL Server.

In order to calculate the obligations according to IAS 19, we have to use life tables which shows, for each age, the probability "that a person of that age will die before his or her next birthday" (Wikipedia). Those probabilities are segregated by male or female.

I have a table with the data of the participants of some benefit plan and also have the life table with the q(x) calculated for each age from 0 to 120 years of age.

Basically, when I need to calculate the obligations for a person, let's say, aged 54 I have to grow this person older in the simulation from 54 to 120. If the person is 25, it goes from 25 to 120. To summarize, I have to make the person go from their current age to the last age of a life table (Let me know if I am not being clear enough here).

I achieved this result by making a LEFT JOIN with the life table to the left. To make the age increment by one I used the following, so the age would not be frozen in the current age:

dbo.ComputeAge(bp.[BirthDate], getdate()) + row_number() over (order by bp.Id) - 1 as age

I am not able to calculate for everybody on my database (~15,000 rows) at once. I didn't pass to this stage yet.

My problem is that I also must compute a variable called tpx which depends directly on its previous value and the previous value of q(x).

For the first row, independently of the age of the beneficiary, tpx is always 1, then it is calculated using it's previous value and the previous value of q(x).

Let's give an example:

Male person aged 54: q(x), according to the life table, is 0.0025568.

enter image description here

In the year 2020, tpx is 1. In the year 2021, tpx is tpx[t-1 = 2020] * ( 1- q(x)[t-1 = 2020])

My first attempt of calculating tpx was

case when (row_number() over (order by id)) = 1 then
    1
else
    lag(tpx, 1)  over (order by id) * (1 - lag(qx, 1) over (order by id))
end as tpx

I prepared a fiddle for this: http://sqlfiddle.com/#!18/bae0a/6

Also, sorry for my bad English.

4
  • 1
    Male person aged 54: q(x), according to the life table, is 0.0025568. But in the table that follow that statement, I see a different qx value 0.01190 ?
    – Squirrel
    Mar 20, 2020 at 1:55
  • I am so sorry, Squirrel! I uploaded the wrong life table. I'll update my fiddle. But It's important to know that Nick from StackOverflow just helped me to solve my problem here. I just need to find out how to calculate all IDs now. Thank you! Mar 20, 2020 at 2:00
  • 1
    Good to know. For computation that involved previous value in multiplication or division, you need a while loop or CTE
    – Squirrel
    Mar 20, 2020 at 2:02
  • I had no luck with WHILE loops. I still have a lot to learn and people like and Nick are so helpful. I feel joy when I meet people llike you. Thank you! I updated the fiddle: UPDATE #2 === ###Fiddle updated according to the observations made by @Nick: sqlfiddle.com/#!18/e0ef2c/2. I'm gonna close this question, so it won't get on the way of people trying to help others. Mar 20, 2020 at 2:16

1 Answer 1

0

So, I cross posted this question on StackOverflow and the user @Nick came with a different approach that I didn't know and that closed the case for me.

Here is his approach:

WITH cte AS (
  SELECT lt.Age,
         bp.Gender,
         CASE WHEN bp.Gender = 'M' THEN CAST(lt.Male as NUMERIC(7,5))
              ELSE CAST(lt.Female as NUMERIC(7,5))
         END AS qx,
         CAST(1.0 AS NUMERIC(7,5)) AS tpx,
         CAST(1.0 AS NUMERIC(7,5)) AS VX
  FROM LifeTable lt
  LEFT JOIN BenefitPlanParticipants bp on lt.Age = dbo.ComputeAge(bp.[BirthDate], GETDATE())
  WHERE bp.Id = 1
  UNION ALL
  SELECT cte.Age + 1,
         Gender,
         CASE WHEN Gender = 'M' THEN CAST(lt.Male as NUMERIC(7,5))
              ELSE CAST(lt.Female AS NUMERIC(7,5))
         END,
         CAST(tpx * (1.0 - qx) AS NUMERIC(7,5)),
         CAST(vx / 1.03370 AS NUMERIC(7,5))
  FROM LifeTable lt
  JOIN cte ON cte.Age + 1 = lt.Age
  WHERE cte.Age < 120
)
SELECT *
FROM cte

The post on StackOverflow can be found here: https://stackoverflow.com/questions/60766448/update-1-how-to-refer-to-the-previous-value-of-a-computed-column-in-sql-serve

THANK YOU GUYS!

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