SQL Earned Premiums for Triangulations

Query to generate earned premiums, working based off the latest of either the entry date or the inception date

Contains

• Temporary Tables
• Calculated Columns
• variables
• While Loops

-- ================================================================================
-- Create date: 08.01.2015
-- Description: Split premium by number of days in the policy and shown when it is earned/entered
--
-- Modifications:
-- 08/01/2015 - First Draft
-- ================================================================================


/*Example Data*/
SET NOCOUNT ON
CREATE TABLE #RiskExample
    (
      RiskID BIGINT IDENTITY(1, 1) ,
      PolicyID VARCHAR(255) ,
      InceptionDate DATE ,
      ExpiryDate DATE ,
      CreateDate DATE ,
      DateToUse AS CASE WHEN CreateDate > InceptionDate THEN CreateDate
                        ELSE InceptionDate
                   END ,
      GrossWrittenPremium NUMERIC(16, 2)
    )

--Test 1 policy created before inception
INSERT  #RiskExample
        ( PolicyID ,
          InceptionDate ,
          ExpiryDate ,
          CreateDate ,
          GrossWrittenPremium
        )
VALUES  ( 'Test 1' , -- PolicyID - VARCHAR(255)
          '2015-01-01' , -- InceptionDate - DATE 
          '2015-12-31' , -- ExpiryDate - DATE 
          '2014-12-15' , -- CreateDate - DATE 
          100  -- GrossWrittenPremium - NUMERIC
        )
--Test 2 policy created after inception
INSERT  #RiskExample
        ( PolicyID ,
          InceptionDate ,
          ExpiryDate ,
          CreateDate ,
          GrossWrittenPremium
        )
VALUES  ( 'Test 2' , -- PolicyID - VARCHAR(255)
          '2015-01-01' , -- InceptionDate - DATE 
          '2015-12-31' , -- ExpiryDate - DATE 
          '2015-01-15' , -- CreateDate - DATE 
          100  -- GrossWrittenPremium - NUMERIC
        )
--Test 3 MTA after inception
INSERT  #RiskExample
        ( PolicyID ,
          InceptionDate ,
          ExpiryDate ,
          CreateDate ,
          GrossWrittenPremium
        )
VALUES  ( 'Test 3' , -- PolicyID - varchar(255)
          '2015-01-01' , -- InceptionDate - date
          '2015-12-31' , -- ExpiryDate - date
          '2015-06-01' , -- CreateDate - date
          100  -- GrossWrittenPremium - numeric
        )
--SET NOCOUNT OFF
GO


/*Query*/
DECLARE @RiskCount BIGINT = 1 ,
    @MaxRisk BIGINT


SELECT  @MaxRisk = MAX(RiskID) --Work out how many policies to iterate through
FROM    #RiskExample

CREATE TABLE #RiskDataSet --create a resultset
    (
      DataSetID BIGINT IDENTITY(1, 1) ,
      RiskID BIGINT ,
      PolicyID VARCHAR(255) ,
      InceptionDate DATE ,
      ExpiryDate DATE ,
      CreateDate DATE ,
      DateToUse DATE ,
      EarnedorEnteredDate DATE ,
      GrossWrittenPremium NUMERIC(16, 2) ,
      EarnedGrossWrittenPremium NUMERIC(16, 14) , -- set this field to a large number of decimal places to allow for rounding
      EnteredGrossWrittenPremium NUMERIC(16, 2)
    )

WHILE @RiskCount <= @MaxRisk --iterate for each policy
    BEGIN
        DECLARE @DaysToRun BIGINT ,
            @DayCount BIGINT = 1
        SELECT  @DaysToRun = DATEDIFF(D, DateToUse, ExpiryDate) --work out how many days to iterate for, for each policy record
        FROM    #RiskExample
        WHERE   RiskID = @RiskCount

        --PRINT @DaysToRun
        WHILE @DayCount <= @DaysToRun --iterate for each day in the policy
            BEGIN
                INSERT  #RiskDataSet
                        ( RiskID ,
                          PolicyID ,
                          InceptionDate ,
                          ExpiryDate ,
                          CreateDate ,
                          DateToUse ,
                          EarnedorEnteredDate ,
                          GrossWrittenPremium ,
                          EarnedGrossWrittenPremium ,
                          EnteredGrossWrittenPremium
       )
                        SELECT  RiskID ,
                                PolicyID ,
                                InceptionDate ,
                                ExpiryDate ,
                                CreateDate ,
                                DateToUse ,
                                EarnedorEnteredDate = DATEADD(DAY,
                                                              @DayCount - 1,
                                                              DateToUse) ,
                                GrossWrittenPremium , --overall sum for checking!
                                EarnedGrossWrittenPremium = GrossWrittenPremium
                                / CAST(@DaysToRun AS FLOAT) , --we earn the same amount each day for each iteration
                                EnteredGrossWrittenPremium = CASE
                                                              WHEN DATEADD(DAY,
                                                              @DayCount - 1,
                                                              DateToUse) = DateToUse
                                                              THEN GrossWrittenPremium
                                                              ELSE 0
                                                             END --we only get this premium on the day to use (the first iteration)
                        FROM    #RiskExample
                        WHERE   RiskID = @RiskCount

                SELECT  @DayCount = @DayCount + 1
            END
        SET @RiskCount = @RiskCount + 1
    END

--display results
SELECT  *
FROM    #RiskDataSet
GO

/*Tidy Up*/
DROP TABLE #RiskExample
DROP TABLE #RiskDataSet
GO