SQL SERVER – Function to Calculate Simple Interest

My recent Comprehensive Database Performance Health Check was with large financial organizations that were facing performance problems. After investigating we found that they have one function which was running very expensive. We found that the function was very poorly written and also had a cursor in it (if you are familiar with my blog post, you know that I am not a big fan of cursors). It was very surprising to me that they would write a complicated function. In this blog post let us see a very simple way to write Function to Calculate Simple Interest.

Function to Calculate Simple Interest

Here is the function to calculate simple interest.

CREATE FUNCTION dbo.SimpleInterest(@Principal DECIMAL(18,2) = 0.0, 
					@InterestRate DECIMAL(18,2) = 0.0, 
					@TimeinYears DECIMAL(18,2) = 0.0)
RETURNS INT 
AS
BEGIN
	RETURN (@Principal * @InterestRate * @TimeinYears / 100)
END
GO

You can now run the function as follows. For example, if you have a principal of 10,000 and the rate of interest is 8.5% per year, let us calculate the interest for 3 years.

SELECT dbo.SimpleInterest(10000,8.5,3) TotalInterest

The result will be 2550. If you want to know the total amount, you will have to add a principal to this interest amount and that will be a total of 12550.

Well, that’s it for today.

Let me know if you are interested to know more about this topic. I will write more blogs as well as create an SQL in Sixty Seconds video.

Here are my few recent videos and I would like to know what is your feedback about them.

• Bitwise Puzzle – SQL in Sixty Seconds 160
• Find Expensive Queries – SQL in Sixty Seconds #159
• Case-Sensitive Search – SQL in Sixty Seconds #158
• Wait Stats for Performance – SQL in Sixty Seconds #157
• Multiple Backup Copies Stripped – SQL in Sixty Seconds #156

Reference: Pinal Dave (http://blog.SQLAuthority.com)