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));
INSERT INTO dbo.Triangle
VALUES ('20190101', 274027.39),
FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS N (N)),
(SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
FROM N AS N1,
N AS N2),
(SELECT TOP 36 T.EffectiveMonth,
ROW_NUMBER() OVER (ORDER BY T.EffectiveMonth ASC) AS MonthNo
FROM dbo.Triangle AS T),
(SELECT DATEADD(MONTH, T.I - 1, MIN(M.EffectiveMonth) OVER ()) AS EffectiveMonth,
T.I AS MonthNo,
M.FundsTaken / 12 AS PremiumEarned
FROM Tally AS T
LEFT JOIN Months AS M ON T.I = M.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,
ORDER BY N1.MonthNo ASC;
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?
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.