• Marek Vavrovic

Using T-SQL to create a bank loan statement


CREATE PROC sp_LoanAmount

(

--Supplying default values

@LoanAmount Money = 1000,

@RateOfInterest decimal(5,2) = 5,

@TimePeriod tinyint = 1

)

AS

BEGIN


DECLARE @LoanDate date

SET @LoanDate = GETDATE()


--Interest (P x R x T)/100; P: Principal (Loan Amount), R: Rate of Interest, T: Time period


DECLARE @Interest money

SET @Interest =( @LoanAmount * @TimePeriod * @RateOfInterest ) / 100


--Grant Total


DECLARE @TotalAmount money

SET @TotalAmount = @LoanAmount + @Interest


--Equated monthly installment (EMI)


DECLARE @EMIAmount money

SET @EMIAmount = @TotalAmount / (@TimePeriod * 12)


--Define date range for the report


DECLARE @StartDate date = @LoanDate;

DECLARE @EndDate date = DATEADD(MONTH,@TimePeriod * 12, @LoanDate)


--Generate one column of serie numbers


;WITH SequenceTable(SeqID) AS

(

SELECT 1

UNION ALL

SELECT SeqID + 1

FROM SequenceTable

WHERE SeqID < DATEDIFF(MONTH, @StartDate, @EndDate)

)

--add a CTE that translates those numbers into the dates in our range


, DateTable(CalendarDates) as

(

SELECT DATEADD(MONTH, SeqID, @StartDate)

FROM SequenceTable

),


--returning a range of dates


ResultTable as

(

select * from DateTable

)


--Adding Sequence Number, EMIAmount and OutstandingBalance to the data set.


SELECT

ROW_NUMBER() OVER (ORDER BY CalendarDates) SeqNo,

CalendarDates ,

CASE WHEN CalendarDates > GETDATE() THEN @EMIAmount ELSE 0 END AS EMIAmount,

SUM(@emiamount)

OVER(ORDER BY CalendarDates ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) OutstandingPrincipal,

FORMAT( SUM(@emiamount)

OVER(ORDER BY CalendarDates ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) / SUM(@TotalAmount),'P') [OutstandingPrincipal%]

FROM DateTable

GROUP BY CalendarDates

ORDER BY CalendarDates

OPTION (MAXRECURSION 0);

END;

GO




EXEC sp_LoanAmount@LoanAmount=1000,@RateOfInterest= 5,@TimePeriod= 1

Loan amount =£1.000

Rate of interest = 5%

Time period = 1 Year





32 views0 comments