SQL Server Running Total – How to Use Running Total Aggregates for 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 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