Whenever working in Financial Applications like Banking applications or financial modules in any ERP application we might have seen as Simple Interest. Usually we do those calculations in the programming application or else in the Frontend side and pass that calculated value to the database tables.
But we can also do the same simple interest calculation in SQL Server, for that we have to create User Defined Functions. Suppose, if we have to calculate simple interest which Merging the data from one table to another table or else moving values from one table to another, by the time we no need to pass the values from SQL Server. Alternatively we can create our own function and pass the values. In this article we will discuss “How to calculate Simple Interest using User Defined Function in SQL Server” .
Function to Calculate Simple Interest
First let’s create a user defined function to calculate Simple Interest,
CREATE FUNCTION Details.CalculateSimpleInterest ( @pPrincipalAmount DECIMAL(18,2), @pInterestRate DECIMAL(18,2), @pDuration DECIMAL(18,2) ) RETURNS INT AS BEGIN RETURN (@pPrincipalAmount*@pInterestRate*@pDuration/100) END
From the above T-SQL Statement,
- @pPrincipalAmount – Pass Principal Amount
- @pInterestRate – Pass the Rate of Interest Per Year
- @pDuration – Duration of the Interest to be calculated
When these values are passed we will get the Simple Interest Rate to paid as result. Lets pass values to the above function and execute as,
SELECT Details.CalculateSimpleInterest(100000,9.5,5) AS [Simple Interest Rate]
GitHub : The GitHub repo for this is here
In this article, we discussed about the calculating the Simple Interest in SQL Server using User Defined Functions. I hope you all found this article much useful. Please share your feedbacks in the comment section. We will discuss more about efficient ways of using T-SQL statements in upcoming articles.