Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
1  
I find in situations like this, it helps to describe the problem in English. It appears that you understand how to use the enhancements to the OVER clause to perform the running sum calculation. Your root problem then is that the logic you have for computing PaymentAllocated is incorrect? –  billinkc Feb 7 at 21:56
    
@billinkc Correct. That is, PaymentAllocated is incorrect for the second data set. I have updated my description with a more concise problem description. –  Jonathan Sapp Feb 8 at 16:17

1 Answer 1

up vote 1 down vote accepted

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

share|improve this answer
    
Excellent answer. Thank you very much! –  Jonathan Sapp Feb 11 at 18:43

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.