0

I have two tables one Loan table and another RePayment table.

enter image description here enter image description here

I want the output like the below table:

enter image description here

Below are some queries which I have tried:

Interest = Principle * Interest Rate/360* Tenure;
Tenure = datediff(ExpiryDate-RePaymentDate);
and Out = (Principle+interest)-RePayment;
CREATE TABLE [dbo].[Loan](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [AccountNo] [nvarchar](50) NOT NULL,
    [Principle] [decimal](18, 2) NOT NULL,
    [InterestRate] [decimal](18, 2) NOT NULL,
    [ExpiryDate] [smalldatetime] NOT NULL)

INSERT [dbo].[Loan] ([Id], [AccountNo], [Principle], [InterestRate], [ExpiryDate]) 
VALUES (1, N'ABC', CAST(1564256.56 AS Decimal(18, 2)), CAST(9.00 AS Decimal(18, 2)), CAST(N'2020-10-08 00:00:00' AS SmallDateTime))

CREATE TABLE [dbo].[RePayment1](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [LoadId] [int] NOT NULL,
    [RePayment] [decimal](18, 2) NOT NULL,
    [SubmitDate] [smalldatetime] NOT NULL) 

INSERT [dbo].[RePayment] ([Id], [LoadId], [RePayment], [SubmitDate]) 
VALUES (1, 1, CAST(300000.00 AS Decimal(18, 2)), CAST(N'2020-12-30 00:00:00' AS SmallDateTime))
    
SELECT AccountNo, ExpiryDate, Principle, (Principle*InterestRate/100/360*Tenure) as Interest, Tenure, SubmitDate, RePayment, ((Principle + (Principle*InterestRate/100/360*Tenure))-RePayment) AS [Out]  
FROM 
    (SELECT Loan.AccountNo, Loan.ExpiryDate, Loan.Principle, Loan.InterestRate, RePayment.SubmitDate, RePayment.RePayment, DATEDIFF(D, Loan.ExpiryDate, RePayment.SubmitDate)+1 AS Tenure
    FROM Loan left outer join RePayment on Loan.Id=RePayment.LoadId)t

enter image description here

I'm stuck, please help me

3
  • 2
  • I see you were able to create all columns, where is the problem? – Francesco Mantovani Jan 16 at 21:12
  • I need the output like "Interest Calculation". Its similar or all the information while inputting another value on RePayment on 31/12/2020 RePayment- 200000. I failed get output. – H A S I B Jan 17 at 10:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.