Sithara J TCS - 2 months ago 5x
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.

Source (Stackoverflow)