Sql-Server

將多筆付款分配到發票行

  • October 16, 2018

我在將付款分配到發票行時遇到問題。我們正在使用 MsSql 2014

數據如下所示：

發票行表（銷售）：

lineId   invoiceId   value
1          1         100
2          1         -50
3          1          40
4          2         500

付款表（付款）：

paymentId   invoiceId   amount
    1          1          50
    2          1          40
    3          2          300

現在，我想知道每個發票行的付款詳細資訊。付款應首先分配給最小值（即第 2 行，-50）

輸出應如下所示：

 lineId   invoiceId   value   paymentId   valuePaid   valueUnpaid
   2           1        -50        1          -50        0
   3           1        40         1          40         0
   1           1        100        1          60         40
   1           1        100        2          40         0
   4           2        500        3          300        200

該問題已在下面的文章中解決，但如果您的發票值為負數或您必須將發票行拆分為兩筆付款，則該解決方案不起作用。

如何在查詢中使用執行總計來輸出財務累積？

這是我到目前為止根據上面的文章所做的：

drop table dbo.#sales
drop table dbo.#payments 
           CREATE TABLE dbo.#sales
           (   lineId       int primary key,           -- unique line id
               invoiceId         int not null ,  -- InvoiceId foreign key
               itemValue      money not null  )       -- value of invoice line.


           CREATE TABLE dbo.#payments 
           (   paymentId       int primary key,        -- Unique payment id
               InvoiceId       int not null,           -- InvoiceId foreign key
               PayAmount          money not null
           )

           -- Example invoice, id #1, with 3 lines, total ammount = 90; id #2, with one line, value 500 

           INSERT dbo.#sales VALUES 
               (1, 1, 100),
               (2, 1, -50), 
               (3, 1, 40),
               (4, 2, 500) ;

           -- Two payments paid towards invoice id#1, 50+40 = 90
           -- One payment paid towards invoice id#2, 300


           INSERT dbo.#Payments
           VALUES  (1, 1, 50),
                   (2, 1, 40),

                   (3, 2, 300);

           -- Expected output should be as follows, for reporting purposes.
           /* lineId, invoiceId, value, paymentId, valuePaid, valueUnpaid
           2, 1, -50, 1, -50, 0
           3, 1, 40, 1, 40, 0
           1, 1, 100, 1, 60, 40
           1, 1, 100, 2, 40, 0
           4, 2, 500, 3, 300, 200 */


           WITH inv AS
             ( SELECT lineId, invoiceId, 
                   itemValue, 
                   SumItemValue = SUM(itemValue) OVER 
                   (PARTITION BY InvoiceId 
                    ORDER BY ItemValue Asc
                    ROWS BETWEEN UNBOUNDED PRECEDING
                             AND CURRENT ROW)
               FROM dbo.#Sales 
               )
           ,  pay AS
             ( SELECT 
                 PaymentId, InvoiceId, PayAmount as PayAmt,
                 SumPayAmt = SUM(PayAmount) OVER 
                   (PARTITION BY InvoiceId 
                    ORDER BY PaymentId
                    ROWS BETWEEN UNBOUNDED PRECEDING
                             AND CURRENT ROW)
               FROM dbo.#payments 
             )



               SELECT 
               inv.lineId,
               inv.InvoiceId,
               inv.itemValue,
               pay.PaymentId,
               PaymentAllocated = 
                 CASE WHEN SumPayAmt <= SumItemValue - itemValue
                        OR SumItemValue <= SumPayAmt - PayAmt
                 THEN 0
                 ELSE
                     CASE WHEN SumPayAmt <= SumItemValue THEN SumPayAmt      
                          ELSE SumItemValue END                             
                   - CASE WHEN SumPayAmt-PayAmt <= SumItemValue-itemValue        
                          THEN SumItemValue-itemValue                          
                          ELSE SumPayAmt-PayAmt END
                 END
           FROM inv JOIN pay
             ON inv.InvoiceId = pay.InvoiceId
           ORDER BY 
               inv.InvoiceId,
               pay.PaymentId;

目前輸出為：

lineId    InvoiceId    itemValue    PaymentId    PaymentAllocated    
 2           1        -50.00         1              0.00
 3           1        40.00          1              0.00
 1           1        100.00         1              50.00
 2           1        -50.00         2              0.00
 3           1        40.00          2              0.00
 1           1        100.00         2              40.00
 4           2        500.00         3              300.00

任何方向將不勝感激。謝謝你。

有關分配規則的更多資訊：

  • 將第一筆付款分配給最小的銷售（即-50）只是確保所有銷售線都能獲得付款的慣例。如果我任意分配或使用其他規則分配，並且第 1 行（值 100）將獲得第一筆付款，我將使用該行的所有付款，而發票的其餘部分將保持未分配狀態。
  • 正如我所說，這只是一個約定。如果其他人提出了不同的規則，那沒關係。實際上，與生產表相比，結構簡化了：付款也有付款日期、類型……並且正確的分配應該告訴我們在每個付款時間支付了哪些發票行。
  • 付款受系統邏輯限制，小於發票行的總和。好吧，可能是付款金額更大的情況：總發票為負數（即：-100）。在這種情況下，我們可以在付款表中插入 -100 範圍內的金額：0 並且總付款限制為 -100

最後，我找到了一個非常簡單自然的解決方案——根據每筆付款在發票總價值中的百分比來分配付款。

                   drop table dbo.#sales
       drop table dbo.#payments

       CREATE TABLE dbo.#sales
       (   lineId       int primary key,           -- unique line id
           invoiceId         int not null ,  -- InvoiceId foreign key
           itemValue      money not null  )       -- value of invoice line.


       CREATE TABLE dbo.#payments 
       (   paymentId       int primary key,        -- Unique payment id
           InvoiceId       int not null,           -- InvoiceId foreign key
           PayAmount          money not null
       )

       -- Example invoice, id #1, with 3 lines, total ammount = 90; id #2, with one line, value 500 

       INSERT dbo.#sales VALUES 
           (1, 1, 100),
           (2, 1, -50), 
           (3, 1, 40),
           (4, 2, 500) ;

       -- Two payments paid towards invoice id#1, 50+40 = 90
       -- One payment paid towards invoice id#2, 300

       INSERT dbo.#Payments
       VALUES  (1, 1, 50),
               (2, 1, 40),
               (3, 2, 300);

       SELECT 
           s.lineId,
           s.InvoiceId,
           s.itemValue,
           p.PayAmount,
           p.PaymentId,
           round(p.PayAmount / ts.SumItemValue,3) as PaymentPercent,
           s.ItemValue  * round(p.PayAmount  / ts.SumItemValue,3) as AllocatedPayment
       FROM dbo.#sales s 
       LEFT JOIN dbo.#payments p 
         ON s.InvoiceId = p.InvoiceId
       LEFT JOIN (SELECT invoiceId, sum(itemValue) as SumItemValue FROM dbo.#sales GROUP BY invoiceId) ts 
           ON s.invoiceId = ts.invoiceId
       ORDER BY 
           s.InvoiceId,
           p.PaymentId;

結果看起來像這樣：

lineId  InvoiceId   itemValue   PayAmount   PaymentId   PaymentPercent  AllocatedPayment
1   1   100.00  50.00   1   0.556   55.60
2   1   -50.00  50.00   1   0.556   -27.80
3   1   40.00   50.00   1   0.556   22.24
3   1   40.00   40.00   2   0.444   17.76
2   1   -50.00  40.00   2   0.444   -22.20
1   1   100.00  40.00   2   0.444   44.40
4   2   500.00  300.00  3   0.60    300.00

引用自：https://dba.stackexchange.com/questions/220016

相關問答

Sql-Server

將遞歸 CTE 過濾為視圖

  • July 15, 2022
檢視問答
Sql-Server

SQL Server CTE 自下而上遞歸與 Where 子句

  • July 7, 2021
檢視問答
Sql-Server

如何在不使用靜態組 ID 的情況下動態執行 join 語句？

  • June 1, 2021
檢視問答
Sql-Server

遞歸 CTE 中的字元串操作

  • February 26, 2021
檢視問答
Sql-Server

遞歸 CTE 多級

  • November 13, 2020
檢視問答
Sql-Server

找不到祖先的 CTE 查詢

  • August 26, 2020
檢視問答