Automatically add rows based on schedule

Question

I am currently building reports off a legacy system in SQL. The data is stored like the below.

Which I have now unpivoted, so this information is stored as a row per contract.

Here is what I am struggling to achieve, I am trying to build a payment schedule table for these contracts. The majority of them are fine as the "FirstPaymentDate" is the full payment we expect, however for some of them we receive the payment every 30 days until the contract is paid off.

So, in the example above, there is a contractvalue of 1000 and a sold term of 2 years. 1000/24 months = £41.6 per month. I need to display this as a table with each date that we expect payment up until the last date, such as the below

Not going to lie, I am struggling how to approach this. I have tried searching but not sure what labels I would search for to cover my issue.

Can anyone help with an approach to solve this? Even if it's just pointing me in the right direction?

Answer 1

You could do this with a recursive CTE. The code below outputs the desired result set you have shown. Your question asks for payments every 30 days but the desired result set example shows every month. This example shows a payment every month. If you need every 30 days you can update to DATEADD(DAY, 30, PaymentDate).

DROP TABLE IF EXISTS #Contract;
CREATE TABLE #Contract
(
    ContractId INT
    , SoldTerm INT
    , FirstPaymentDate DATE
    , ContractValue MONEY
    , PaymentTerms INT
);

INSERT INTO #Contract VALUES (10222, 2, '01/04/2022', 1000, 30);

WITH ContractDates AS
(
    SELECT ContractId, FirstPaymentDate AS PaymentDate, 1 AS Payment, (ContractValue / (SoldTerm * 12)) AS PaymentAmount, (SoldTerm * 12) AS LastPayment
    FROM #Contract
    UNION ALL
    SELECT ContractId, DATEADD(MONTH, 1, PaymentDate), Payment + 1 AS Payment, PaymentAmount, LastPayment
    FROM ContractDates
    WHERE Payment + 1 <= LastPayment
)

SELECT * FROM ContractDates;

DROP TABLE IF EXISTS #Contract;