Recursive CTE to SUM amount column using date column as terminating condition

I have a table containing loan data including the start date, loan term (how many months the loan will last), and monthly payment.
I am trying to create a recursive CTE that will return the SUM of payments made each month for the past 12 months.

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]
GO

Sample Data:

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),
    ('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),
    ('11/6/2019', 64, 65),
    ('10/13/2017', 52, 128)
GO

Here’s what I have tried:

WITH loanCTE as 
(
-- Anchor Member
SELECT monthly_payment, DATEADD(month, loan_term, starting_date) as loan_end_date, starting_date, starting_date as current_month
    FROM loan

UNION ALL
-- Recursive Member
SELECT monthly_payment, loan_end_date, starting_date, DATEADD(month, 1, current_month)
    FROM loanCTE
    WHERE current_month < loan_end_date -- Terminating condition
)

SELECT  YEAR(current_month) as Year, MONTH(current_month) as Month,  SUM(monthly_payment) as total_montly_payments
    FROM loanCTE
    WHERE Current_Month BETWEEN GETDATE()-365 AND GETDATE()
    GROUP BY YEAR(current_month), MONTH(current_month) 
    ORDER BY YEAR(current_month) DESC, MONTH(current_month) DESC;

which returns this:

Year    Month   total_montly_payments
2020    8   12001.00
2020    7   12193.00
2020    6   12193.00
2020    5   12193.00
2020    4   12201.00
2020    3   12201.00
2020    2   12201.00
2020    1   12201.00
2019    12  12201.00
2019    11  12201.00
2019    10  12136.00
2019    9   12136.00
2019    8   136.00

I can tell this is wrong because 2019-08 should have 12202.00 in total_monthly_payments and 2020-08 should have 12194.00 in total_monthly_payments, but I’m not sure where I’m going wrong. It seems either the way I increment the current month in the recursive member or something with the final query is off, but I am currently stuck. Any advice is greatly appreciated!

Answer

I think your query is working fine as you are filtering the latest one year records, so the current month is filtered first (actually the issue is happening because of the day value in current_month, we should use the first date of month to get the correct results) and then aggregation is performed on the resulting set. You can combine the records first and then apply filter later like below:

;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
    FROM loan
UNION ALL
-- Recursive Member
SELECT monthly_payment, loan_end_date, starting_date, DATEADD(MM, 1, current_month)
    FROM loanCTE
    WHERE current_month < loan_end_date -- Terminating condition
)
,
temp as
(
    SELECT  YEAR(current_month) as [Year], MONTH(current_month) as [Month],  SUM(monthly_payment) as total_montly_payments
    FROM loanCTE
    GROUP BY YEAR(current_month), MONTH(current_month) 
)

SELECT *
FROM temp
WHERE datefromparts(YEAR, MONTH, 1) BETWEEN GETDATE()-365 AND GETDATE()
ORDER BY [Year] DESC, [Month] DESC

Also, for 201908, the total would be 12136.0 from the following:

(‘4/3/2016’, 72, 12000), –12000

(‘9/15/2017’, 31, 8), –8

(’10/13/2017′, 52, 128) –128

Please find the db<>fiddle here.