1

I'm moving this question from SO

Some years ago I was asked to create something to calculate the current value of a lot of insurance policies, I came out with a query that worked fine, but I was working with SQLServer 2000, and it was quite a monster.
Two days ago, as exercise, I rewrote that query with the capabilities of SQLServer 2012, the readability is like night and day, but that is the only improvement, is there something else that can be done? Or is there a better/different way to get the wanted result?

Tables
The tables are reduced to show only the needed data. In the real environment there was a table Policies_Lines with every money movement (payment or withdrawal) that form the policies cash value and history, and the Policies table had not the Value column as it was calculated from the lines

CREATE TABLE Products (
  ID INT IDENTITY(1, 1)
, Name VARCHAR(30)
, MinRate DECIMAL(3, 2)
)

CREATE TABLE AnnualInterests (
  Product_ID INT
, Term  INT
, Rate DECIMAL(3, 2)
)

CREATE TABLE Policies (
  ID INT IDENTITY(1, 1)
, Product_ID INT
, EffectiveDate DATETIME
, EndDate DATETIME
, Value MONEY
)

This SQLFiddle link has those tables, some data and my query.

Rules

  • There are different product, every product have a minimum interest rate, if the yearly rate is lower the minimum rate is used, e.g. min Rate = 1.2%, yearly rate = 1% the interest is 1.2%
  • The interest rate for every product are indipentend and they change every year
  • If a insurance policy have the end date NULL is current, the end date to calculate its value is today.
  • The value is calculated at the end of every year
  • If the policy stands for less than a year the interest rate is (1 + yearly rate)^(percentage of year) .

For example we can have a policy with the rates 1.2%, 0.95%, 1.1% for three following years, if the base value is 1000$, it started at the beginning of the first year and was closed at the end of the last year, and the minimum rate is 1.05% the current value will be

1000$ * (1 + 1.2%) * (1 + 1.05% /*not 0.95%*/) * (1 + 1.1%) = 1033.874886$.  

The same example, with the start date at half year, will be calculated as

1000$ * ((1 + 1.2%)^0.5) * (1 + 1.05% /*not 0.95%*/) * (1 + 1.1%) = 1027.745193$

The query written in SQLServer 2012 is

WITH PInt AS -- Check the product yearly interest for a value lower then the MinRate
(
  SELECT pr.ID
       , ai.Term
       , Interest = (pr.MinRate + ai.Rate) / 2
                  + ABS(pr.MinRate - ai.Rate) / 2
  FROM   Products pr
         LEFT JOIN AnnualInterests ai ON pr.ID = ai.Product_ID
), PoInt AS -- Get all the data and the year fraction
(
SELECT po.ID
     , po.Value
     , po.Product_ID
     , pi.Term
     , Interest = (1 + pi.Interest/100)
     , YearFraction
     = CASE
        WHEN YEAR(po.EffectiveDate) = YEAR(COALESCE(po.EndDate, GetDate()))
         THEN DateDiff(day, po.EffectiveDate, COALESCE(po.EndDate, GetDate())) + 1
        WHEN YEAR(po.EffectiveDate) = pi.Term
         THEN DateDiff(day, po.EffectiveDate, DATEFROMPARTS(TERM, 12, 31)) + 1
        WHEN YEAR(Coalesce(po.EndDate, GetDate())) = pi.Term
         THEN DateDiff(day, DATEFROMPARTS(TERM, 1, 1), COALESCE(po.EndDate, GetDate())) + 1
        ELSE DateDiff(day, DATEFROMPARTS(TERM, 1, 1), DATEFROMPARTS(TERM, 12, 31)) + 1
       END
     / (DateDiff(day, DATEFROMPARTS(TERM, 1, 1), DATEFROMPARTS(TERM, 12, 31)) + 1.00)
FROM   Policies po
       INNER JOIN PInt pi ON po.Product_ID = pi.ID 
              AND pi.Term BETWEEN YEAR(po.EffectiveDate) 
                              AND YEAR(COALESCE(po.EndDate, GetDate()))
)
SELECT Product_ID
     , ID
     , Cash = CAST(Value * EXP(SUM(LOG(POWER(Interest, YearFraction)))) as Money)
FROM   PoInt
GROUP BY Product_ID, ID, Value
ORDER BY Product_ID, ID

In this query there are two little math tricks:

  1. To calculate the Max in the first CTE I used the formula (a+b)/2 + ABS(a-b)/2, the geometrical interpretation is the middle point between a and b plus half the distance, changing the sign between the two part will get the Min, from my test is faster then the usual CASE check
  2. The product of the yearly interest rate is transformed into the e^(sum(ln(rate))), as there is not an aggregation function for the productory (the same thing of the summatory for the multiplication) and ln(a*b) -> ln(a) + ln(b) and e^ln(a) -> a

Thanks for any advice.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.