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_ID | AMOUNT | PAYMENT_DATE |
---|---|---|
1 | 15.38 | 5 JAN 2023 |
1 | 15.38 | 5 FEB 2023 |
1 | 15.38 | 5 MAR 2023 |
I am struggling to come up with the best way to achieve this.
2条答案
按热度按时间31moq8wy1#
Consider:
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 |
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 calledTally
: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: