We were working on a financial calculation recently where given two dates, the interest had to be calculated for a given amount. Thank to my colleague Satyam to help me out with the query
Here we are calculating the interest accumulated on the amount 395.80 at the rate of 15% per annum between 09-22-2009 and 07-13-2010. Please note that the interest rate calculated here is based on the business rules we had and the output expected. It may be a different in your case, for which you will have to modify the query.
The formula is as follows
Interest = Amount * InterestRate^(Days/365.25)
DECLARE @StartDate DateTime
DECLARE @EndDate DateTime
DECLARE @Amount Decimal(6,3)
SET @StartDate = '2009-09-22'
SET @EndDate = '2010-07-13'
SET @Amount = 395.80
DATEDIFF(d, @StartDate, @EndDate)/365.25))) - @Amount
In the query shown above, we are using the POWER function which returns the value of the given expression to the specified power. Here are Convert the Date Difference into numeric value to provide it to the POWER function which comes out to be .805 i.e POWER(1.1500, .805). The interest rate is 15%,
Effectively this query comes out to be
SELECT @Amount * (POWER(1.1500, .805)) - @Amount
SELECT @Amount * 1.1191 - @Amount