Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.
Become a Premium Member and unlock a new, free course in leading technologies each month.
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
create table Account(
Account_Number integer
,Current_Balance money
,Final_Maturity_Date datetime
,Interest_Rate decimal(6,4)
)
create table Payment(
Account_Number integer
,Payment_Amount money
,Payment_Frequency integer
,Payment_date datetime
)
insert Account values (1001, 123456, '1/1/2020', 5.125)
insert Account values (1002, 987654, '1/1/2030', 6.5)
insert Account values (1003, 121212, '1/1/2015', 5.75)
-- Account 1001 made an extra payment
insert Payment values(1001, 123, 0, '1/1/2009')
insert Payment values(1001, 123, 0, '2/1/2009')
insert Payment values(1001, 123, 0, '2/15/2009')
insert Payment values(1001, 123, 0, '3/1/2009')
-- Account 1002 skipped a payment
insert Payment values(1002, 987, 0, '1/1/2009')
insert Payment values(1002, 987, 0, '3/1/2009')
-- Account 1003 has not yet made a payment
select
a.Account_Number
, a.Current_Balance
, Periodic_Interest =
Current_Balance * ((Interest_Rate/100)/12)
, New_Balance =
Current_Balance + (Current_Balance * ((Interest_Rate/100)/12))
, Payment_Amount = isnull(p.Payment_Amount,0)
from Account as a
left outer join Payment as p on p.Account_Number = a.Account_Number
/* Input Parameters */
declare @Account_Number int = 1001
declare @Payment_Amount money = 1234
/* Look ups */
declare @Current_Balance money
declare @Final_Maturity_Date datetime
declare @Interest_Rate decimal(6,4)
declare @Payment_Date datetime
select
@Current_Balance = Current_Balance
,@Final_Maturity_Date = Final_Maturity_Date
,@Interest_Rate = Interest_Rate
from Account where Account_Number = @Account_Number
set @Payment_Date = (
select max(Payment_Date) from Payment
where Account_Number = @Account_Number
)
/* Starting Assumptions */
declare @Monthly_Rate decimal(6,4) = @Interest_Rate /100 /12
declare @Balance_Due money = @Current_Balance
declare @Months_ToGo int = datediff(month, @Payment_Date, @Final_Maturity_Date)
/* Only process if loan is not already mature */
if @Months_ToGo > 0 begin
/* Reset months to go, we will figure it out below */
set @Months_ToGo = 0
/* Loop through remaining payments.
Assumption is that interest was already part
of the most recent payment so additional interest
is added starting the next payment.
*/
declare @Interest_Due money = 0
while @Balance_Due > 0 begin
set @Months_ToGo = @Months_ToGo + 1
set @Balance_Due = @Balance_Due - (@Payment_Amount - @Interest_Due)
set @Interest_Due = @Balance_Due * @Monthly_Rate
end
end
select 'Months To Go' = @Months_ToGo
If you are experiencing a similar issue, please ask a related question
Join the community of 500,000 technology professionals and ask your questions.