I have two tables one Loan table and another RePayment table.
I want the output like the below table:
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
I'm stuck, please help me