SQL Server Proportional allocation in sql

gajydyqb  于 12个月前  发布在  其他
关注(0)|答案(2)|浏览(69)

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
hlswsv35

hlswsv351#

Here's a potential solution which doesn't use recursive CTEs:

SELECT  *
INTO #data
FROM
(
    VALUES  (N'2018-12-05', 150.00,150.00)
    ,   (N'2018-12-08', 100.00,100.00)
    ,   (N'2018-12-21', 30.00,30.00)
    ,   (N'2019-01-05', 165.00,165.00)
    ,   (N'2019-01-08', 110.00,110.00)
    ,   (N'2019-01-21', 55.00,55.00)
    ,   (N'2019-02-05', 165.00,165.00)
    ,   (N'2019-02-08', 110.00,110.00)
    ,   (N'2019-02-21', 55.00,55.00)
) t (D_Date,Amount,A_Rest)

DECLARE @x float = 430 -- change amount here

;WITH data AS(
    SELECT  d_date
    ,   monthYearSum
    ,   CASE WHEN accSum <= @x THEN monthYearSum ELSE @x - (accSum - monthyearSum) END AS lagSum
    FROM    (
        SELECT  MIN(D_Date) AS d_date
        ,   SUM(amount) AS monthYearSum
        ,   SUM(SUM(amount)) OVER(ORDER BY year(d_date), MONTH(d_date)) AS accSum
        FROM    #data
        GROUP BY YEAR(d_date), MONTH(d_date)
    ) x
)
SELECT  CASE WHEN lagSum > 0 THEN amount / monthYearSum  * lagSum * 1.0 ELSE 0 END AS Paid
,   CASE WHEN lagSum > 0 THEN amount - (amount / monthYearSum  * lagSum * 1.0) ELSE Amount END AS rest
,   dd.*
FROM    data d
INNER JOIN #data dd
    ON  YEAR(dd.D_Date) = YEAR(d.d_date)
    AND MONTH(dd.d_date) = MONTH(d.d_date)

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 used lagSum .

Edit: alternative version that avoids self join:

WITH data AS(
    SELECT  *
    ,   CASE WHEN accSum <= @x THEN monthYearSum ELSE @x - (accSum - monthyearSum) END AS lagSum
    FROM    (
        SELECT  *
        ,   SUM(case when rnk = 1 then monthYearSum end) OVER(ORDER BY year(d_date), month(d_date) ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS accSum
        FROM    (
            SELECT  SUM(amount) over(partition by year(d_date), month(d_date)) AS monthYearSum
            ,   ROW_NUMBER() OVER(partition by year(d_date), month(d_date) order by @@spid) AS rnk
            ,   *
            FROM    #data
        ) x
    ) x
)
SELECT  CASE WHEN lagSum > 0 THEN amount / monthYearSum  * lagSum * 1.0 ELSE 0 END AS Paid
,   CASE WHEN lagSum > 0 THEN amount - (amount / monthYearSum  * lagSum * 1.0) ELSE Amount END AS rest
,   *
FROM    data d
jdgnovmf

jdgnovmf2#

CREATE TABLE FD_Bills (
  F_Subscr    INT,
  D_Date      DATE,
  Amount      DECIMAL(15,2),
  A_Rest      DECIMAL(15,2)
);

INSERT INTO
  FD_Bills
VALUES
  (1, '2018-12-05', 150.00, 150.00),
  (1, '2018-12-08', 100.00, 100.00),
  (1, '2018-12-21',  30.00,  30.00),
  (1, '2019-01-05', 165.00, 165.00),
  (1, '2019-01-08', 110.00, 110.00),
  (1, '2019-01-21',  55.00,  55.00),
  (1, '2019-02-05', 150.00, 150.00),
  (1, '2019-02-08', 100.00, 100.00),
  (1, '2019-02-21',  30.00,  30.00)
;
DECLARE @payment DECIMAL(15,2) = 611.00;

WITH
  monthly(F_Subscr, D_Date, Amount, month_total, month_to_date, to_date) AS
(
  SELECT
    F_Subscr, D_Date, Amount,
    SUM(Amount) OVER (PARTITION BY F_Subscr, YEAR(D_Date), MONTH(D_Date)),
    SUM(Amount) OVER (PARTITION BY F_Subscr, YEAR(D_Date), MONTH(D_Date)
                          ORDER BY D_Date),
    SUM(Amount) OVER (PARTITION BY F_Subscr
                          ORDER BY D_Date)
  FROM
    FD_Bills
),
  calculations AS
(
  SELECT
    *,
    @payment - (to_date - month_to_date)   AS payment_for_this_month
  FROM
    monthly
)
SELECT
  *,
  CASE
    WHEN payment_for_this_month <= 0           THEN Amount
    WHEN payment_for_this_month >= month_total THEN 0
    ELSE
      Amount
      -
      payment_for_this_month * Amount / month_total
  END
    AS A_Rest
FROM
  calculations  
WHERE
  F_Subscr = 1
ORDER BY
  F_Subscr,
  D_Date
F_SubscrD_DateAmountA_Rest
12018-12-05150.000.000000
12018-12-08100.000.000000
12018-12-2130.000.000000
12019-01-05165.000.000000
12019-01-08110.000.000000
12019-01-2155.000.000000
12019-02-05150.00149.464286
12019-02-08100.0099.642858
12019-02-2130.0029.892858

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

-- Payment for a row == Payment for all rows  UPTO  this one
--                      MINUS
--                      Payment for all rows BEFORE this one

This ensures that when rows are rounded, they all add up to exactly the right amount.

DECLARE @payment DECIMAL(15,2) = 611.00;

WITH
  monthly(F_Subscr, D_Date, Amount, month_total, month_to_date, to_date) AS
(
  SELECT
    F_Subscr, D_Date, Amount,
    SUM(Amount) OVER (PARTITION BY F_Subscr, YEAR(D_Date), MONTH(D_Date)),
    SUM(Amount) OVER (PARTITION BY F_Subscr, YEAR(D_Date), MONTH(D_Date)
                          ORDER BY D_Date),
    SUM(Amount) OVER (PARTITION BY F_Subscr
                          ORDER BY D_Date)
  FROM
    FD_Bills
),
  calculations AS
(
  SELECT
    *,
    @payment - (to_date - month_to_date)   AS payment_for_this_month
  FROM
    monthly
)
SELECT
  *,
  CASE
    WHEN payment_for_this_month <= 0           THEN Amount
    WHEN payment_for_this_month >= month_total THEN 0
    ELSE
      Amount
      -
      CAST(
        ROUND(payment_for_this_month * (month_to_date         ) / month_total, 2)
        -
        ROUND(payment_for_this_month * (month_to_date - Amount) / month_total, 2)
        AS
        DECIMAL(15,2)
      )
  END
    AS A_Rest
FROM
  calculations  
WHERE
  F_Subscr = 1
ORDER BY
  F_Subscr,
  D_Date
F_SubscrD_DateAmountA_Rest
12018-12-05150.000.00
12018-12-08100.000.00
12018-12-2130.000.00
12019-01-05165.000.00
12019-01-08110.000.00
12019-01-2155.000.00
12019-02-05150.00149.46
12019-02-08100.0099.65
12019-02-2130.0029.89

fiddle

相关问题