How can I use running total aggregates in a query to output financial accumulations?

I am attempting to write a query that will be consumed by a SSRS 2012 report. Given a paid invoice, with a certain set of account allocations towards which the money paid towards it must be saved, I must apply the payments towards each account's allocation and distribute the payments into the expected account allocations.

The allocations for one invoice should not affect the other allocations for other invoices. I have succeeded in isolating the partitioned aggregates to each invoice. I just can't seem to get it to work in the case where each payment amount is less than one of the expected account amounts.

My preference is for the calculations to be performed within a single select statement and not row-by-agonizing row in a cursor.

EDIT: The problem is: distribute all the invoice's payments into the required accounts such that the account allocations are fulfilled. Neither any particular order of payment-to-account nor payment-type-to-account is required. The example script I wrote outputs zero allocation for the payment if the payment has fully been allocated prior or the account allocation for that invoice has been fulfilled by another payment, but that (the zero allocation) is not required in the output (and will be ignored, anyway, in the report output itself).

```
/ 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 s

Interesting problem. Here is my attempt.

First we calculate a running total of the Allocation.InvoiceAmt over the various 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 
  )

and the same for 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 
  )

Then we combine the previous two CTEs:

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 ;

Test at SQL-Fiddle

StackExchange Database Administrators Q#58474, answer score: 3