Sithara J TCS Sithara J TCS - 8 months ago 40
SQL Question

Price Annualizing in SQL

Hi I am using the following calculation to annualize a price field.

(price * 365 / close _date - begin_date ) * 9.35/100

This calculation is working when leap year is involved

for example when price = 6000

begin date = 1-jan- 2016

close date = 31-dec-2017

so the annualized value is 280.50

But when I change begin dates & end date to 1-jan-2017 and 31-dec-2018, the value changes to 280.88

How do I get the values to 280.50 in both the cases?can any averaging be done so that the value comes perfectly when any date is involved ?


You may want to use months_between():

select price  *  12 / months_between(close _date, begin_date ) *  9.35/100

You may have to adjust the boundaries by one day for it to work for your dates:

select price  *  12 / months_between(close _date + 1, begin_date) *  9.35/100

Note: months_between() is a little tricky because it returns fractional months. However, if the difference is always to the first of the month, then you are safe.