The essence of the task is to make a distribution on available debts by D_Date (year, month).
- D_Date -- Amount A_Rest
------------------------------
2018-12-05 150,00 150,00
2018-12-08 100,00 100,00
2018-12-21 30,00 30,00
2019-01-05 165,00 165,00
2019-01-08 110,00 110,00
2019-01-21 55,00 55,00
2019-02-05 165,00 165,00
2019-02-08 110,00 110,00
2019-02-21 55,00 55,00
With input @payment = 200,00 Expect result:
- D_Date -- Amount A_Rest
-------------------------------
2018-12-05 150,00 42,84
2018-12-08 100,00 28,58
2018-12-21 30,00 8,58 __ 201812
2019-01-05 165,00 165,00
2019-01-08 110,00 110,00
2019-01-21 55,00 55,00 __ 201901
2019-02-05 165,00 165,00
2019-02-08 110,00 110,00
2019-02-21 55,00 55,00 __ 201902
As a result, only 1 month (there are 3 rows in first month) was used, because the amount in @payment was not enough to pay all debts
@payment = 200 I tried to solve this problem in the following way - output the sum of debts by month (SUM)A_Rest OVER (PARTITION BY D_Date) AS total and then Payment - total, as a result I got just data that cannot be converted to a proportional distribution over the table.
Amount - amount of a particular payment
Total - total amount of all payments (280)
Initial_Amount - initial amount of payment (200)
Substituting values:
For a line with date 2018-12-05:
A_Rest_new = (150 / 280) * 200 = 107.14 ≈ 42.84
For the row with date 2018-12-08:
A_Rest_new = (100 / 280) * 200 = 71.43 ≈ 28.58
For the row with date 2018-12-21:
A_Rest_new = (30 / 280) * 200 = 21.43 ≈ 8.58
That is, the amount in the A_Rest column is distributed in proportion to the payment amounts in the Amount column relative to the original payment amount of 200.
declare @payment money = 200.00;
declare @F_Subscr int = 1;
SELECT DISTINCT
d_date
,N_Amount
,N_Amount_Rest - ((N_Amount_Rest / SUM(N_Amount_Rest) OVER (PARTITION BY d_date) ) * @payment ) as N_Amount_Rest
FROM
(
SELECT
FORMAT(D_Date, 'yyyyMM') d_date
,N_Amount
,N_Amount_Rest
FROM
dbo.FD_Bills
WHERE
F_Subscr = @F_Subscr
) tt
The code above result:
D_Date -N_Amount-N_Amount_Rest
-----------------------------
201812 30,00 8,58
201812 100,00 28,58
201812 150,00 42,86
201901 55,00 21,68
201901 110,00 43,34
201901 165,00 65,00
201902 55,00 21,68
201902 110,00 43,34
201902 165,00 65,00
Scenarios:
- If @payment = 280:
- D_Date -- Amount A_Rest
------------------------------
2018-12-05 150,00 0,00
2018-12-08 100,00 0,00
2018-12-21 30,00 0,00
2019-01-05 165,00 165,00
2019-01-08 110,00 110,00
2019-01-21 55,00 55,00
2019-02-05 165,00 165,00
2019-02-08 110,00 110,00
2019-02-21 55,00 55,00
- if @payment = 430:
- D_Date -- Amount A_Rest
------------------------------
2018-12-05 150,00 0,00
2018-12-08 100,00 0,00
2018-12-21 30,00 0,00
2019-01-05 165,00 90,00
2019-01-08 110,00 60,00
2019-01-21 55,00 30,01
2019-02-05 165,00 165,00
2019-02-08 110,00 110,00
2019-02-21 55,00 55,00
- if @payment = 940:
- D_Date -- Amount A_Rest
------------------------------
2018-12-05 150,00 0,00
2018-12-08 100,00 0,00
2018-12-21 30,00 0,00
2019-01-05 165,00 0,00
2019-01-08 110,00 0,00
2019-01-21 55,00 0,00
2019-02-05 165,00 0,00
2019-02-08 110,00 0,00
2019-02-21 55,00 0,00
2条答案
按热度按时间hlswsv351#
Here's a potential solution which doesn't use recursive CTEs:
I create an aggregated month / year data CTE which contains amount per month/year as well as accumulated amount per month/year.
For each month/year period, you want to know how much money can actually be distributed. This is calculated by:
CASE WHEN accSum <= @x THEN monthYearSum ELSE @x - (accSum - monthyearSum) END
, either total monthyear amount, or part of it just covering @x.Finally, you do the distribution by creating distribution of amount
(amount / monthYearSum)
and multiplying with the actual amount usedlagSum
.Edit: alternative version that avoids self join:
jdgnovmf2#
Note, if rounded to 2 decimal places, all three
A_Rest
rows round Down. This means that 0.01 would go missing. There are numerous scenarios where this rounding error would occur.If that matters, use the version below...
Same method as above, with one difference
This ensures that when rows are rounded, they all add up to exactly the right amount.
fiddle