Set-based approach to distributing a value SSMS TSQL

Question

I have a table that includes a total debt and an annual repayment rate, payment start date and a calculated value for the first years prorata payment. I need to create/amend the table so that the payment schedule for each ID is set out in rows, with the first years pro-rata payment happening at the start date, and then subsequent rows/years payments being made at the payment rate until the debt is totally paid.

For example for the first ID, The debt is 100 to be repaid at 24 annually. The first payment (2) is to be made in 2023. The next payment and all other payments save any residual will be 24 (the full repayment rate) until all debt is accounted for.

I've tried various approaches, most of them trying to use some kind of iteration or loop which i was crucified for. I think windows functions might be something but i'm struggling to understand how to do that in this context. I'm definitely not getting my head around how to begin to approach this as a set-based problem - I tend to think procedurally which doesn't seem like the correct approach.

Any and all help appreciated.

Equally if a set-based approach isn't the way to go for this problem- help with an alternative would be great.

CREATE TABLE #full (
ID NVARCHAR(10),
StartDate DATE,
[Total Debt] INT,
RepaymentRate INT,
[Yr 1 partial Rate] INT,
Amount INT);

INSERT INTO #full
VALUES
(1, '2023-01-01',   100,    24, 2,   0),
(2, '2024-01-01',   100,    11, 5,   0),
(3, '2025-01-01',   100,    32, 7,   0),
(4, '2026-01-01',   100,    9,  2,   0),
(5, '2027-01-01',   100,    5,  24,  0)

Expected output:

:ID:	:Start:	:Total Debt:	:Rate:	:Yr1Rate:	:Amount:	: PayYear :
1	01/01/2023	100	24	2	2	01/01/2023
1	01/01/2023	100	24	2	24	01/01/2024
1	01/01/2023	100	24	2	24	01/01/2025
1	01/01/2023	100	24	2	24	01/01/2026
1	01/01/2023	100	24	2	24	01/01/2027
1	01/01/2023	100	24	2	2	01/01/2028
2	01/01/2024	100	11	5	5	01/01/2024

Answer 1

I use recursive CTE in the end:

--Get first year's prorata delivery sorted first
WITH RecurCTE AS (
    SELECT
        ID, StartDate, [Total Debt], RepaymentRate,
        [Yr 1 partial Rate] AS Amount, --Provide the pro_rata value as the amount
        [Total Debt] - [Yr 1 partial Rate] AS RemainingDebt --Calculate a remaining figure to be delivered after a row's delivery
    FROM #full
    --Rescursive element - adding a row per each years projected delivery
    UNION ALL
       SELECT
       c.ID,
        DATEADD(YEAR, 1, c.StartDate) AS StartDate,
        c.[Total Debt],
        c.RepaymentRate,
        -- where the amount still to be accounted for minus the rate is more than zero
        --(e.g. the full rate should be used for this row and not any residual figure) Amount = full rate
        --Otherwise if the remainder is less than the full rate, Amount = remaining value
        CASE
            WHEN c.RemainingDebt - c.RepaymentRate > 0 THEN c.RepaymentRate
            ELSE c.RemainingDebt
        END AS Amount,

        --Recaulcate remainder to reflect allocation made above (note this is current remainder minus same logic above for latest allocation)
        c.RemainingDebt - CASE
            WHEN c.RemainingDebt - c.RepaymentRate > 0 THEN c.RepaymentRate
            ELSE c.RemainingDebt
        END AS RemainingDebt
    FROM
        RecurCTE c
    WHERE --keep going until remaining left to allocate to a year is zero
        c.RemainingDebt > 0
)

-- Selecting the result from the CTE with a reasonable recursion limit
SELECT
    ID,
    StartDate,
    RepaymentRate,
    Amount
FROM
    RecurCTE
ORDER BY
    ID,
    StartDate
OPTION (MAXRECURSION 100);