By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select @@version;
| (No column name) |
|---|
| Microsoft SQL Server 2019 (RTM-CU16) (KB5011644) - 15.0.4223.1 (X64) Apr 11 2022 16:24:07 Copyright (C) 2019 Microsoft Corporation Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor) |
CREATE TABLE [dbo].[loan](
[id] [int] IDENTITY(1,1) NOT NULL,
[Starting_Date] [date] NULL,
[Loan_Term] [int] NULL,
[Monthly_Payment] [decimal](19, 2) NULL
) ON [PRIMARY]
INSERT INTO [dbo].[loan]
([Starting_Date]
,[Loan_Term]
,[Monthly_Payment])
VALUES
('8/1/2020', 52, 1),
('6/2/2010', 64, 2500),
('9/11/2011', 66, 650),
('7/4/2011', 36, 600),
('11/19/2014', 36, 450),
('4/21/2017', 24, 650),
('6/25/2009', 68, 7500),
('4/3/2016', 72, 12000), --12000
('5/2/2013', 12, 330),
('7/11/2011', 32, 331.33),
('6/11/2013', 36, 200),
('7/11/2014', 34, 250),
('8/15/2015', 32, 673),
('9/15/2017', 31, 8), --8
('11/6/2019', 64, 65),
('10/13/2017', 52, 128) --128
--SELECT * FROM dbo.loan l
--ORDER BY id, starting_date
;WITH loanCTE as
(
-- Anchor Member
SELECT monthly_payment, DATEADD(MM, loan_term, starting_date) as loan_end_date, starting_date, starting_date as current_month
| Year | Month | total_montly_payments |
|---|---|---|
| 2024 | 5 | 66.00 |
| 2024 | 4 | 66.00 |
| 2024 | 3 | 66.00 |
| 2024 | 2 | 66.00 |
| 2024 | 1 | 66.00 |
| 2023 | 12 | 66.00 |
| 2023 | 11 | 66.00 |
| 2023 | 10 | 66.00 |
| 2023 | 9 | 66.00 |
| 2023 | 8 | 66.00 |
| 2023 | 7 | 66.00 |
| 2023 | 6 | 66.00 |
| 2023 | 5 | 66.00 |