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

我正在嘗試編寫一個將由 SSRS 2012 報告使用的查詢。給定一張已付款的發票，其中必須保存一組帳戶分配，支付給它的錢必須保存，我必須將付款應用於每個帳戶的分配，並將付款分配到預期的帳戶分配中。

一張發票的分配不應影響其他發票的其他分配。我已經成功地將分區聚合隔離到每張發票。在每筆付款金額小於預期賬戶金額之一的情況下，我似乎無法讓它發揮作用。

我的偏好是在單個選擇語句中執行計算，而不是在游標中逐行處理。

編輯：問題是：將所有發票的付款分配到所需的帳戶中，以便完成帳戶分配。既不需要任何特定的帳戶付款順序，也不需要帳戶付款類型。如果付款之前已完全分配或該發票的帳戶分配已由另一筆付款完成，則我編寫的範例腳本輸出付款的零分配，但輸出中不需要（零分配）（並將無論如何，在報告輸出本身中被忽略）。

/* SQL 2012 SP1 */

DECLARE @Allocation TABLE
(   InvoiceId       int not null,           -- The invoice id under which the money was charged.
   Account         varchar(20) not null ,  -- An external system identifier used for reporting purposes
   InvoiceAmt      money not null  )       -- The amount that needs to be deposited into the account.


DECLARE @Payment TABLE
(   PaymentId       int primary key,        -- Unique payment id
   InvoiceId       int not null,           -- InvoiceId foreign key
   PaymentType     varchar(20) not null,   -- check/cash/credit
   CheckNumber     varchar(20),
   Amount          money not null
)

-- Example invoice, id#1 with a total of $40 that must be split three ways into separate accounts

INSERT @Allocation
VALUES  (1, 'FIMS-A', 10),                  -- $10 (25%) of the total should go into FIMS-A
       (1, 'FIMS-B', 10),                  -- $10 (25%) of the total should go into FIMS-B
       (1, 'FIMS-C', 20)                   -- $20 (50%) of the total should go into FIMS-C

-- Two payments paid towards invoice id#1

INSERT @Payment
VALUES  (1, 1, 'CHECK', '12345', 20),
       (2, 1, 'CHECK', '56789', 20)

-- Expected output should be as follows, for reporting purposes.
/*
* InvoiceId PaymentId Account PaymentAllocated
* 1         1         FIMS-A  10.00
* 1         1         FIMS-B  10.00
* 1         1         FIMS-C   0.00
* 1         2         FIMS-A   0.00
* 1         2         FIMS-B   0.00
* 1         2         FIMS-C  20.00
*/

   -- This sql query selects the correct expected output results.

   SELECT  p.InvoiceId,
           p.PaymentId,
           a.Account,
           IIF(COUNT(*) OVER (PARTITION BY p.InvoiceId ORDER BY p.PaymentId) = 1,
               p.Amount,
               ABS(p.Amount -
                   SUM(IIF(p.Amount > a.InvoiceAmt,
                           p.Amount - a.InvoiceAmt,
                           a.InvoiceAmt)) OVER (PARTITION BY a.Account, p.InvoiceId ORDER BY p.PaymentId))) AS PaymentAllocated

     FROM  @Payment p
LEFT JOIN  @Allocation a
       ON  a.InvoiceId = p.InvoiceId
 ORDER BY  p.PaymentId,
           a.Account

-- Example invoice, id#2 with a total of $330 that must be split two ways into separate accounts

INSERT @Allocation
VALUES  (2, 'FIMS-A', 165),                 -- $165 of the total should go into FIMS-A
       (2, 'FIMS-B', 165),                 -- $165 of the total should go into FIMS-B
       (3, 'FIMS-A', 100)                  -- $100 of Invoice #3 goes into FIMS-A

-- Two payments totaling $330
INSERT @Payment
VALUES  (3, 2, 'check', 'abc', 40.00),
       (4, 2, 'check', '123', 290.00),
       (5, 3, 'check', '673', 100.00)

-- Expected output should be as follows, for reporting purposes.
/*
* InvoiceId PaymentId Account  PaymentAllocated
* 1         1         FIMS-A   10.00
* 1         1         FIMS-B   10.00
* 1         1         FIMS-C    0.00
* 1         2         FIMS-A    0.00
* 1         2         FIMS-B    0.00
* 1         2         FIMS-C   20.00
*
* 2         3         FIMS-A   40.00       
* 2         3         FIMS-B    0.00
* 2         4         FIMS-A  125.00
* 2         4         FIMS-B  165.00
*
* 3         5         FIMS-A  100.00
*/

   -- This is the same query as above, but the addition of the second data set (invoice id#2) into the tables
   -- produces inaccurate accumulations. The third invoice (id#3 is correct)
   SELECT  p.InvoiceId,
           p.PaymentId,
           a.Account,
           IIF(COUNT(*) OVER (PARTITION BY p.InvoiceId ORDER BY p.PaymentId) = 1,
               p.Amount,
               ABS(p.Amount -
                   SUM(IIF(p.Amount > a.InvoiceAmt,
                           p.Amount - a.InvoiceAmt,
                           a.InvoiceAmt)) OVER (PARTITION BY a.Account, p.InvoiceId ORDER BY p.PaymentId))) AS PaymentAllocated

     FROM  @Payment p
LEFT JOIN  @Allocation a
       ON  a.InvoiceId = p.InvoiceId
 ORDER BY  p.PaymentId,
           a.Account

有趣的問題。這是我的嘗試。

Allocation.InvoiceAmt首先，我們計算各種的總和InvoiceId：

WITH inv AS
 ( SELECT 
     InvoiceId, Account, 
     InvAmt = InvoiceAmt,
     SumInvAmt = SUM(InvoiceAmt) OVER 
       (PARTITION BY InvoiceId 
        ORDER BY Account
        ROWS BETWEEN UNBOUNDED PRECEDING
                 AND CURRENT ROW)
   FROM Allocation 
 ) 

和同樣的Payment.Amount：

, pay AS
 ( SELECT 
     PaymentId, InvoiceId, PaymentType, CheckNumber, 
     PayAmt = Amount,
     SumPayAmt = SUM(Amount) OVER 
       (PARTITION BY InvoiceId 
        ORDER BY PaymentId
        ROWS BETWEEN UNBOUNDED PRECEDING
                 AND CURRENT ROW)
   FROM Payment 
 )

然後我們結合前面兩個CTE：

SELECT 
   inv.InvoiceId,
   pay.PaymentId,
   inv.Account,
   PaymentAllocated = 
     CASE WHEN SumPayAmt <= SumInvAmt - InvAmt
            OR SumInvAmt <= SumPayAmt - PayAmt
     THEN 0
     ELSE
         CASE WHEN SumPayAmt <= SumInvAmt THEN SumPayAmt      -- these would be
              ELSE SumInvAmt END                              -- simpler if there
       - CASE WHEN SumPayAmt-PayAmt <= SumInvAmt-InvAmt       -- was a LEAST() and 
              THEN SumInvAmt-InvAmt                           -- a GREATEST() function
              ELSE SumPayAmt-PayAmt END
     END
FROM inv JOIN pay
 ON inv.InvoiceId = pay.InvoiceId
ORDER BY 
   inv.InvoiceId,
   pay.PaymentId,
   inv.Account ;

在**SQL-Fiddle進行測試**

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