May 20, 2019 at 2:36 pm
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));
GO
INSERT INTO dbo.Triangle
VALUES ('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);
GO
WITH
N 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 CumulativeEarned
FROM 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 NULL
GROUP BY N1.EffectiveMonth,
N1.MonthNo,
N1.FundsTaken
ORDER BY N1.MonthNo ASC;
GO
DROP 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.
Larnu.uk
May 20, 2019 at 4:09 pm
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?
May 20, 2019 at 5:04 pm
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 #Triangle
VALUES ('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 PremiumEarned
FROM #Triangle t
CROSS JOIN Age a
CROSS APPLY ( VALUES(DATEADD(MONTH, a.Age, t.EffectiveMonth)) ) AS ef(EffectiveMonth)
GROUP BY ef.EffectiveMonth
;
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
May 21, 2019 at 7:57 am
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.
Larnu.uk
May 21, 2019 at 9:14 am
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.
Larnu.uk
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply