SQL Claims Triangles

Insurance claims triangles, getting the latest outstanding amount and the total amount paid

Includes

• Temporary Tables
• Updating tables using a join
• While Statements
• Alter statements

-- ================================================================================
-- Author: Chris Johnson
-- Create date: 08.01.2015
-- Description: Get the latest outstanding amount and sum all previously incurred amounts
--
-- Modifications:
-- 08/01/2015 - CJ  - First Draft
-- ================================================================================
/*Example Data*/
SET NOCOUNT ON
CREATE TABLE #ClaimExample
    (
      ClaimMovementID BIGINT IDENTITY(1, 1) ,
      PolicyID VARCHAR(255) ,
      ClaimID VARCHAR(255) ,
      MovementID VARCHAR(255) ,
      InceptionDate DATE ,
      ExpiryDate DATE ,
      MovementDate DATE ,
      Incurred NUMERIC(16, 2) ,
      Outstanding NUMERIC(16, 2)
    )

/*Test 1 - All Outstanding amount on initial entry*/
INSERT  #ClaimExample
        ( PolicyID ,
          ClaimID ,
          MovementID ,
          InceptionDate ,
          ExpiryDate ,
          MovementDate ,
          Incurred ,
          Outstanding
        )
VALUES  ( 'TESTa' , -- PolicyID - varchar(255)
          'TEST' , -- ClaimID - varchar(255)
          'Test1' , --MovementID - varchar(255)
          '2015-01-01' , -- InceptionDate - date
          '2015-12-31' , -- ExpiryDate - date
          '2015-02-01' , -- MovementDate - date
          0 , -- Incurred - numeric
          1000  -- Outstanding - numeric
        )

/*Test 2 - Partially Settled claim amount*/
INSERT  #ClaimExample
        ( PolicyID ,
          ClaimID ,
          MovementID ,
          InceptionDate ,
          ExpiryDate ,
          MovementDate ,
          Incurred ,
          Outstanding
        )
VALUES  ( 'TESTa' , -- PolicyID - varchar(255)
          'TEST' , -- ClaimID - varchar(255)
          'Test2' , --MovementID - varchar(255)
          '2015-01-01' , -- InceptionDate - date
          '2015-12-31' , -- ExpiryDate - date
          '2015-06-01' , -- MovementDate - date
          1000 , -- Incurred - numeric
          500  -- Outstanding - numeric
        )

/*Test 3 - Fully Settled claim amount*/
INSERT  #ClaimExample
        ( PolicyID ,
          ClaimID ,
          MovementID ,
          InceptionDate ,
          ExpiryDate ,
          MovementDate ,
          Incurred ,
          Outstanding
        )
VALUES  ( 'TESTa' , -- PolicyID - varchar(255)
          'TEST' , -- ClaimID - varchar(255)
          'Test3' , --MovementID - varchar(255)
          '2015-01-01' , -- InceptionDate - date
          '2015-12-31' , -- ExpiryDate - date
          '2015-09-01' , -- MovementDate - date
          2000 , -- Incurred - numeric
          0  -- Outstanding - numeric
        )
/*Test 4 - Fully Settled claim amount on another policy*/
INSERT  #ClaimExample
        ( PolicyID ,
          ClaimID ,
          MovementID ,
          InceptionDate ,
          ExpiryDate ,
          MovementDate ,
          Incurred ,
          Outstanding
        )
VALUES  ( 'TESTb' , -- PolicyID - varchar(255)
          'TEST' , -- ClaimID - varchar(255)
          'Test3' , --MovementID - varchar(255)
          '2015-01-01' , -- InceptionDate - date
          '2015-12-31' , -- ExpiryDate - date
          '2015-09-01' , -- MovementDate - date
          2000 , -- Incurred - numeric
          0  -- Outstanding - numeric
        )

/*Query*/

/*Identify all unique policy and claims*/
CREATE TABLE #MasterPolicyTable
    (
      RiskID BIGINT IDENTITY(1, 1) ,
      PolicyID VARCHAR(255) ,
      ClaimID VARCHAR(255)
    )
INSERT  #MasterPolicyTable
        ( PolicyID ,
          ClaimID
        )
        SELECT DISTINCT
                PolicyID ,
                ClaimID
        FROM    #ClaimExample

CREATE TABLE #Test
    (
      PolicyID VARCHAR(255) ,
      ClaimID VARCHAR(255) ,
      MovementDate DATE ,
      Incurred NUMERIC(16, 2) ,
      Outstanding NUMERIC(16, 2) ,
      DayPaid DATE
    )


/*Work out how many policies to iterate through*/
DECLARE @TotalClaims BIGINT ,
    @ClaimCount BIGINT = 1

SELECT  @TotalClaims = COUNT(1)
FROM    #MasterPolicyTable

WHILE @ClaimCount <= @TotalClaims
    BEGIN
--Work out number of days to iterate through for each claim (number of days in a policy)
        DECLARE @InceptionDate DATE ,
            @ExpiryDate DATE ,
            @DaysToRun BIGINT ,
            @DayCount BIGINT= 0
        SELECT  @InceptionDate = InceptionDate ,
                @ExpiryDate = ExpiryDate ,
                @DaysToRun = DATEDIFF(DAY, InceptionDate, ExpiryDate)
        FROM    #ClaimExample CE
                INNER JOIN #MasterPolicyTable MPT ON MPT.ClaimID = CE.ClaimID
                                                     AND MPT.PolicyID = CE.PolicyID
        WHERE   MPT.RiskID = @ClaimCount

        WHILE @DayCount <= @DaysToRun
            BEGIN
                INSERT  #Test
                        ( PolicyID ,
                          ClaimID ,
                          MovementDate ,
                          Incurred ,
                          Outstanding ,
                          DayPaid
       )
                        SELECT  CE.PolicyID ,
                                CE.ClaimID ,
                                MovementDate = MAX(CASE WHEN MovementDate <= DATEADD(DAY,
                                                              @DayCount,
                                                              InceptionDate)
                                                        THEN CE.MovementDate
                                                        ELSE NULL
                                                   END) ,
                                Incurred = SUM(CASE WHEN MovementDate <= DATEADD(DAY,
                                                              @DayCount,
                                                              InceptionDate)
                                                    THEN Incurred
                                                    ELSE 0
                                               END) ,
                                Outstanding = CAST(NULL AS NUMERIC(16, 2)) ,
                                DayPaid = DATEADD(DAY, @DayCount,
                                                  InceptionDate)
                        FROM    #ClaimExample CE
                                INNER JOIN #MasterPolicyTable MPT ON MPT.ClaimID = CE.ClaimID
                                                              AND MPT.PolicyID = CE.PolicyID
                        WHERE   MPT.RiskID = @ClaimCount
                        GROUP BY CE.PolicyID ,
                                CE.ClaimID ,
                                InceptionDate ,
                                ExpiryDate

                UPDATE  #Test
                SET     Outstanding = COALESCE(CE.Outstanding,0)
                FROM    #Test T
                        LEFT JOIN #ClaimExample CE ON CE.ClaimID = T.ClaimID
                                                      AND CE.MovementDate = T.MovementDate
                                                      AND CE.PolicyID = T.PolicyID

                SELECT  @DayCount = @DayCount + 1
            END


        SELECT  @ClaimCount = @ClaimCount + 1
    END

ALTER TABLE #Test
ADD TotalIncurred AS Incurred+Outstanding

SELECT  *
FROM    #Test

/*Tidy up*/
DROP TABLE #Test
DROP TABLE #ClaimExample
DROP TABLE #MasterPolicyTable
--DROP TABLE #MovementList