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
Title | # Comments | Views | Activity |
---|---|---|---|
why sql server only update some statistics in the database ? | 3 | 22 | |
Find SQL query used by application | 3 | 20 | |
SQL Error - Query | 6 | 26 | |
T-SQL Default value in Select? | 5 | 27 |
Join the community of 500,000 technology professionals and ask your questions.