SQL Server Create a payment schedule based on 2 dates

vybvopom  于 2023-03-22  发布在  其他
关注(0)|答案(2)|浏览(93)

I have a table that looks like the below.
| CONTRACT_ID | AMOUNT | FIRST_PAYMENT | LAST_PAYMENT |
| ------------ | ------------ | ------------ | ------------ |
| 1 | 200 | 5 JAN 2023 | 5 JAN 2024 |

I am trying to build a payment schedule that shows the monthly payment amount between the FIRST_PAYMENT and LAST_PAYMENT . The monthly amount will be the ANOUNT divided by the number of months between the first and last payment. It should look like the below. (just using 3 rows for example)

CONTRACT_IDAMOUNTPAYMENT_DATE
115.385 JAN 2023
115.385 FEB 2023
115.385 MAR 2023

I am struggling to come up with the best way to achieve this.

31moq8wy

31moq8wy1#

Consider:

DECLARE @Contracts TABLE (Contract_id INT, Amount INT, First_Payment DATE, Last_Payment DATE)
INSERT INTO @Contracts (Contract_id, Amount, First_Payment, Last_Payment) VALUES
(1, 200,  '5 JAN 2023', '5 JAN 2024'),
(2, 1000, '5 JAN 2023', '5 JUN 2023');

;WITH payments AS (
SELECT Contract_id, First_Payment AS PaymentDate, Last_Payment
  FROM @Contracts
UNION ALL
SELECT Contract_id, DATEADD(MONTH,1,PaymentDate), Last_Payment
  FROM payments
 WHERE PaymentDate < Last_Payment
)

SELECT c.Contract_id, CAST(c.Amount AS DECIMAL(10,2)) / COUNT(p.PaymentDate) OVER (PARTITION BY p.Contract_id ORDER BY (SELECT 1)) AS Payment, p.PaymentDate
  FROM payments p
    INNER JOIN @Contracts c
      ON p.Contract_id = c.Contract_id

I took your example data, added a row, and converted it into example DDL/DML. As has already been pointed out in the comments, there are 13 payments, resulting in a equal payment of 15.38 over 13 months for the original example.

Here we're using a recursive CTE to generate a row per payment date for each contract. Then we can simply join back to the contract table. Using a windowed COUNT function we can get the total number of payments per contract and divide the original amount by them (after converting the int to a decimal).
| Contract_id | Payment | PaymentDate |
| ------------ | ------------ | ------------ |
| 1 | 15.3846153846153 | 2023-02-05 |
| 1 | 15.3846153846153 | 2023-03-05 |
| 1 | 15.3846153846153 | 2023-04-05 |
| 1 | 15.3846153846153 | 2023-05-05 |
| 1 | 15.3846153846153 | 2023-06-05 |
| 1 | 15.3846153846153 | 2023-07-05 |
| 1 | 15.3846153846153 | 2023-08-05 |
| 1 | 15.3846153846153 | 2023-09-05 |
| 1 | 15.3846153846153 | 2023-10-05 |
| 1 | 15.3846153846153 | 2023-11-05 |
| 1 | 15.3846153846153 | 2023-12-05 |
| 1 | 15.3846153846153 | 2024-01-05 |
| 1 | 15.3846153846153 | 2023-01-05 |
| 2 | 166.6666666666666 | 2023-01-05 |
| 2 | 166.6666666666666 | 2023-02-05 |
| 2 | 166.6666666666666 | 2023-03-05 |
| 2 | 166.6666666666666 | 2023-04-05 |
| 2 | 166.6666666666666 | 2023-05-05 |
| 2 | 166.6666666666666 | 2023-06-05 |

q5lcpyga

q5lcpyga2#

This is much easier with some kind of Tally function. Unfortunately, as you're on SQL Server 2016, you don't have access to GENERATE_SERIES so I use a "helper" function called Tally :

CREATE FUNCTION [fn].[Tally] (@LastNumber bigint, @Zero bit) 
RETURNS table
AS RETURN

    WITH N AS(
        SELECT N
        FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
    Tally AS(
        SELECT 0 AS I
        WHERE @Zero = 0
          AND @LastNumber IS NOT NULL
        UNION ALL
        SELECT TOP (ISNULL(@LastNumber,0))
               ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
        FROM N N1, N N2, N N3, N N4, N N5, N N6, N N7) --Up to 10,000,000 rows
    SELECT I

One thing to note, however, is that when working with (what are presumably) monetary values you can't just go ahead and divide these into equal parts. Take your example of 200 in 13 payments, 200 / 13 = 15.38461538461538 but you can't ask someone to pay you 0.461~ pence/cents/etc per payment. As such what you'll need to do is ensure that any "lost" pennies are added to one of the payments. I add them to the first payment.

This results in a little more a complicated problem. First you split the value into 13 payments, based on the number of months+1 difference in the payment dates. Then in the bottom part of the query I check that the SUM of the values is equal to that of the expected total; if it isn't I add the remainder to the first payment. This result in the first payment for the example being 15.44, and all subsequent payments being 15.38:

SELECT *
INTO dbo.YourTable
FROM (VALUES(1,200.,CONVERT(date,'20230105'),CONVERT(date,'20240105')),
            (2,120.,CONVERT(date,'20230105'),CONVERT(date,'20241205')))V(ContractID, Amount, FirstPayment, LastPayment);
GO
WITH Amounts AS(
    SELECT YT.ContractID,
           CONVERT(decimal(16,2),YT.Amount / COUNT(1) OVER (PARTITION BY ContractID)) AS Amount,
           YT.Amount AS TotalAmount,
           DATEADD(MONTH, T.I, FirstPayment) AS PaymentDate,
           T.I
    FROM dbo.YourTable YT
        CROSS APPLY fn.Tally(DATEDIFF(MONTH,FirstPayment,LastPayment),0) T)
SELECT ContractID,
       --Amount,
       CASE TotalAmount WHEN SUM(Amount) OVER (PARTITION BY ContractID) THEN Amount
            ELSE CASE I WHEN 0 THEN TotalAmount - SUM(Amount) OVER (PARTITION BY ContractID ORDER BY I
                                                                    ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING)
                        ELSE Amount
                 END
       END AS Amount,
       PaymentDate
FROM Amounts A
ORDER BY ContractID,
         PaymentDate;
GO

DROP TABLE dbo.YourTable;

相关问题