I use a recursive CTE
;WITH cte (
-- anchor
UNION ALL
-- recursive parte
) SELECT * INTO #ReservaREBAtivos FROM cte OPTION(MAXRECURSION 0);?
to get this sample rowset then I use the following query to return the data from the temp table create:
SELECT
Chave
,Ano
,Idade
,qx
,qy
,qxi
,ix
,vx
,vx_pec
,tpx
,tpy
,tpx_apo
,tpxaa
,tpxaa_apo
,Aposentadoria
,Peculio
,Invalidez
,Peculioativo
,PeculioInvalidez
,RateioPuc
,RateioCustoNormal
,ROW_NUMBER() OVER (PARTITION BY Chave ORDER BY Ano) AS rn
FROM #ReservaREBAtivos ORDER BY Chave, Ano;
The code above brings the result of the CTE I talked previously.
Note that the column "Chave" (Key, in Portuguese) is the primary key of the original table in which each row represents the data of one employee. So in that file we only have one "Chave/Key."
In that sample file from GitHub I have calculations for when the employee reaches ages from his/her current age to 120 years, so that file has the data of only one employee who's currently 42 years old (column "Idade", "Year" in Portuguese). I used the CTE to recursively calculate the data for the subsequent ages, once the data of one year depends on the data from the previous year, except the first year that uses actual data of the employee himself/herself. From ages 43 to 120 we have projections.
The calculations are going fine, but I'm stuck with the following problem:
I need to calculate 1 column that uses the data from columns "qxi" (6th column), "ix" (7th column), "tpxaa_apo" (14th column) and "PeculioInvalidez" (19th column).
The first row in that column, let's call it "Liability", is qxi * ix * tpxaa_apo * PeculioInvalidez.
From the second value on, the calculation uses the previous value of "Liability" * (1 - LAG(qxi, 1))/LAG(qxi, 1) * qxi + qxi * ix * tpxaa_apo * PeculioInvalidez. That is Row3 in "Liability" uses the value calculated in row2, row4 uses the value from row3 and so on for each "Chave/Key". Row3 = Liability(Row2) * (1-LAG(qxi,1)...
I'm using LAG just to show that the calculation also uses the previous value of "qxi."
When the calculation for one employee ends, it starts the same process again to the next employee and so on.
This is what I tried so far:
Create a new column in the temp table #ReservaREBAtivos:
ALTER TABLE #ReservaREBAtivos ADD Liability DECIMAL(30, 20);
Then I try to calculate the values to the column Liability using a WHILE loop and that's where I'm stuck:
/*==============================================================
==
== See that those values qxi, ix, tpxaa_apo and PeculioInvalidez
== used inside the loop all should come from #ReservaREBAtivos
== to calculate "@PassivoPeculioInvalidez" and then INSERT
== its values into #ReservaREBAtivos.Liability again
==
===========================================================*/
WHILE EXISTS(SELECT * FROM #ReservaREBAtivos)
BEGIN
DECLARE @PassivoPeculioInvalidez DECIMAL(30, 20);
IF (SELECT rn FROM #ReservaREBAtivos) = 1
SET @PassivoPeculioInvalidez = qxi * ix * tpxaa_apo * PeculioInvalidez
ELSE
SET @PassivoPeculioInvalidez = @PassivoPeculioInvalidez * (1 - LAG(qxi) OVER (PARTITION BY Chave ORDER BY Chave))
/ LAG(qxi) OVER (PARTITION BY Chave ORDER BY Chave) * qxi + qxi * ix * tpxaa_apo * PeculioInvalidez
FROM #ReservaREBAtivos
END?
How do I use previous value of something that doesn't even exist to the database?
Should I write a function that uses the result of its previous call? How do I do that? What's the best way?
Thank you, guys, for your time.
I think that you can do that using another CTE.
and I do advise the use of () around the formula blocks so there is no ambiguity of what is calculated first
with cte
as (select chave
, ano
, qxi * ix * tpxaa_apo * PeculioInvalidez as PassivoPeculioInvalidez
, qxi -- required
from tbl
where rn = 1
union all
-- t1.qxi and t1.PassivoPeculioInvalidez are from the previous year calculated value
select t2.chave
, t2.ano
, t1.PassivoPeculioInvalidez * t1.qxi
/ t1.qxi * t2.qxi + t2.qxi * t2.ix * t2.tpxaa_apo * t2.PeculioInvalidez
, t2.qxi
from cte t1
inner join tbl t2
on t2.chave = t1.chave
and t2.ano = t1.ano + 1
)
select *
from cte
May 11, 2020 at 8:13 pm
Wow! That's it! Sorry If I'm asking too much, but would you mind explaining what you did in the recursive part of the CTE? I'm really surprised by the result. It didn't returned like 1,000,000 rows. I only know the very basics of CTEs.
May 11, 2020 at 9:21 pm
lets see if I can
a recursive CTE works by having a "anchor" set - that is the sql before the union. - this is the start point for all records being processed by the CTE
the union part joins the CTE with other tables (inner join)
once the join happens it becomes a new anchor - this repeats until all records are read, it reaches the max recursion possible (either to o many records or circular references)
so on this particular case you always know that you are going to have a anchor (rn = 1) and that all remaining records for the same key (chave) will also be on the table - and you only interested on those that are equal to the year (ano) of the record from the anchor + 1
as your calculation depends on the values from the previous row (or just the base calc if rn = 1) you pass those on both selects
but on the union part you pass the "current" row calculated values - so the values are available for the next row
May 11, 2020 at 9:47 pm
You're awesome! Thank you very much for your time and helpful comments and for your amazing answer on my question. I was really struggling with this one.
May 12, 2020 at 4:19 pm
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply