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?
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.
- 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
NULLis 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:
- To calculate the Max in the first
CTEI 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
- 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.