CODEWITHSUNDEEP
sqloracle-databasedate
Sithara J TCS
Sithara J TCS

Reputation: 3

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 ?

Upvotes: 0

Views: 391

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270723

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.

Upvotes: 0

John Cappelletti
John Cappelletti

Reputation: 82010

2016 is a Leap Year.. 366 days. While 2017 has 365 days

Your Denom slips from 730 days to 729 days

Upvotes: 1

Related Questions