# Net Earned in Triangles without a Triangular Join?

• Thom A

SSC Guru

Points: 98655

Something I've been trying to figure today is can I achieve a figure for net earned in our insurance triangles without using a triangular join. I'm sure I could do this with a Window Function, but I can't seem to think of a way to do so.

The way the figures work is to do with insurance and that the policy has a period of indemnity; for simplicity we'll say that every policy has a period of indemnity of 12 months. Let's say that a policy incepts in January and has a value of £1,200 (pre tax). At the end of January the net Earned for the policy is £100 (ignore that the policy could incept at any point in January, we're specifically just looking at the month). At the end of February, the policy has earned £200, March £300; you get the idea.

Lets say you then have another policy worth £600 in Feburary. As a result, the net earned value at the end of February is £250 (`2 * (1200 / 12)` from the policy in January and `1 * (600 / 12)` from the policy in February), March would be £400, etc. The way I'm currently working out the cumulative value is using a triangular join. I can't use a `SUM` with a `ROWS BETWEEN` clause on it's own, as I have to multiple the value of the premium earned by the amount of months that have passed +1 (and cap at 12).

With the triangular join, this gives me a solution like this:

`CREATE TABLE dbo.Triangle (EffectiveMonth date,                           FundsTaken decimal(12, 2));GOINSERT INTO dbo.TriangleVALUES ('20190101', 274027.39),       ('20190201', 319976.3),       ('20190301', 234576.66),       ('20190401', 219056.01),       ('20190501', 267799.13),       ('20190601', 279493.83),       ('20190701', 187711.28),       ('20190801', 331990.56),       ('20190901', 259942.14),       ('20191001', 225755.82),       ('20191101', 300988.78),       ('20191201', 266831.51),       ('20200101', 5228.2);GOWITHN   AS    (SELECT N     FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS N (N)),Tally AS    (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I     FROM N AS N1,          N AS N2),Months AS    (SELECT TOP 36 T.EffectiveMonth,            T.FundsTaken,            ROW_NUMBER() OVER (ORDER BY T.EffectiveMonth ASC) AS MonthNo     FROM dbo.Triangle AS T),NPE AS    (SELECT DATEADD(MONTH, T.I - 1, MIN(M.EffectiveMonth) OVER ()) AS EffectiveMonth,            M.FundsTaken,            T.I AS MonthNo,            M.FundsTaken / 12 AS PremiumEarned     FROM Tally AS T          LEFT JOIN Months AS M ON T.I = M.MonthNo)SELECT N1.EffectiveMonth,       N1.MonthNo,       ISNULL(N1.FundsTaken,0) AS FundsTaken,       CONVERT(decimal(12,2),SUM(N2.PremiumEarned * V.Multiplier)) AS CumulativeEarnedFROM NPE AS N1     LEFT JOIN NPE AS N2 ON N1.EffectiveMonth >= N2.EffectiveMonth     CROSS APPLY (VALUES (IIF(((N1.MonthNo - N2.MonthNo) + 1) > 12, 12, ((N1.MonthNo - N2.MonthNo) + 1)))) AS V (Multiplier)WHERE N1.MonthNo IS NOT NULLGROUP BY N1.EffectiveMonth,         N1.MonthNo,         N1.FundsTakenORDER BY N1.MonthNo ASC;GODROP TABLE dbo.Triangle;`

Perhaps this is the way to do it, but it just feels a little wrong. Anyone any ideas, or does this seem like the right way to go?

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

• Matt Miller (4)

SSC Guru

Points: 124208

I think you're making this complicated - by conflating two separate processes.  One is the schedule of earnings (how much from each policy is earned in each accounting period).  You'll need that to deal with all of your edge cases where your earning period may not match with your equity period, or partial months or back/forward dated transactions etc....

Still you end up with a detail table with earnings by month by policy. Essentially:

20190101 P1    100

20190201 P1    100

20190201 P2     50

20190301 P1   100

20190301 P2   50

etc...

Once you split it out this way - your cumulation becomes easy using

SUM() over (order by Effective month.... Rows unbounded preceding)

You can even choose to partition by policy or not.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

• drew.allen

SSC Guru

Points: 76739

I didn't round my results, but otherwise this gives the same results.  I also had to add extra values to the table to get all of the dates that you had.

`CREATE TABLE #Triangle (EffectiveMonth date,							FundsTaken decimal(12, 2));INSERT INTO #TriangleVALUES ('20190101', 274027.39),		('20190201', 319976.3),		('20190301', 234576.66),		('20190401', 219056.01),		('20190501', 267799.13),		('20190601', 279493.83),		('20190701', 187711.28),		('20190801', 331990.56),		('20190901', 259942.14),		('20191001', 225755.82),		('20191101', 300988.78),		('20191201', 266831.51),		('20200101', 5228.2),--  Additional Dates		('20210101', 0),		('20220101', 0),		('20230101', 0),		('20240101', 0),		('20250101', 0),		('20260101', 0),		('20260501', 0);WITH Age AS (	SELECT Age FROM ( VALUES(0), (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11) ) a(Age) )SELECT	ef.EffectiveMonth,	SUM(CASE WHEN Age = 0 THEN FundsTaken ELSE 0 END) AS FundsTaken,	SUM(SUM(FundsTaken / 12)) OVER(ORDER BY ef.EffectiveMonth ROWS UNBOUNDED PRECEDING) AS PremiumEarnedFROM #Triangle tCROSS JOIN Age aCROSS APPLY ( VALUES(DATEADD(MONTH, a.Age, t.EffectiveMonth)) ) AS ef(EffectiveMonth)GROUP BY ef.EffectiveMonth;`

Drew

J. Drew Allen

• Thom A

SSC Guru

Points: 98655

Thanks Drew. I think that's what I'm after. The data above was pre-aggregated, so i'll give a go at applying this to the non-preaggregated but I think this is very much on the right path.

Cheers!

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

• Thom A

SSC Guru

Points: 98655

Yep, that got me to the answer I needed, Drew. My final query using production data is quite different, but the nested `SUM` with the `OVER` is exactly the route I needed.

Would mark as the solution/answer, but seems that feature has been removed from SSC.

Thanks again.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

Viewing 5 posts - 1 through 5 (of 5 total)