Split records in SQL server

Transact-SQL
1

Hello all !! I need help in splitting the records based on the date columns based on the below criteria:
If difference of Pol Eff Dt and Pol Exp Dt are same then --> No action (Keep as it is)
If difference of Pol Eff Dt and Pol Exp Dt is 1 year then --> create 2 records
If difference of Pol Eff Dt and Pol Exp Dt is 2 year then --> create 3 records and so on. That means, the code should be dynamic enough to handle any year period.

The excel formula behind the "%Distribution" column for the row number 24,25 and 26 are:
=DATEDIF(O24, P24, "d")/DATEDIF(O24, P26, "d")
=DATEDIF(O25, P25, "d")/DATEDIF(O24, P26, "d")
=DATEDIF(O26, P26, "d")/DATEDIF(O24, P26, "d")

The excel formula for the "Earned Premium in USD" column is =Q24J24, =Q25J25, =Q26*J26
similarly for the "Earned Commission in USD" column.

Please find the screenshot of the examples. The gray colored columns signifies the original columns (11 nos) from source and the yellow ones are the derived columns (7 nos).

Also, the excel formula are there to help you with the calculation steps.

Its your wish if you want to keep the original table as it is and create a new table with 11+7=18 columns.

I am okay with what ever approach you want to take - I mean T-SQL or Stor Proc.

Pasted image1255×487 26 KB

Regards,
PPG

2

you can make use of a number table

SELECT t.*,
       calendar_year = datepart(year, dateadd(year, n.num, t.eff_date)),
      . . . .
FROM   your_table t
       INNER JOIN number n ON  n.num >= 0
                           AND n.num <= DATEDIFF(year, t.eff_date, t.exp_date)

the rest of the columns, you should be able to use your formula to calcualte it

1 Like