Oracle SQL sum up values till another value is reached

Question

I hope I can describe my challenge in an understandable way. I have two tables on a Oracle Database 12c which look like this:

Table name "Invoices"

I_ID | invoice_number |     creation_date     | i_amount
------------------------------------------------------
  1  |  10000000000   |  01.02.2016 00:00:00  |   30
  2  |  10000000001   |  01.03.2016 00:00:00  |   25
  3  |  10000000002   |  01.04.2016 00:00:00  |   13
  4  |  10000000003   |  01.05.2016 00:00:00  |   18
  5  |  10000000004   |  01.06.2016 00:00:00  |   12

Table name "payments"

P_ID |   reference    |     received_date     | p_amount
------------------------------------------------------
  1  |  PAYMENT01     |  12.02.2016 13:14:12  |   12
  2  |  PAYMENT02     |  12.02.2016 15:24:21  |   28
  3  |  PAYMENT03     |  08.03.2016 23:12:00  |    2
  4  |  PAYMENT04     |  23.03.2016 12:32:13  |   30
  5  |  PAYMENT05     |  12.06.2016 00:00:00  |   15

So I want to have a select statement (maybe with oracle analytic functions but I am not really familiar with it) where the payments are getting summed up till the amount of an invoice is reached, ordered by dates. If the sum of for example two payments is more than the invoice amount the rest of the last payment amount should be used for the next invoice.

In this example the result should be like this:

invoice_number | reference | used_pay_amount | open_inv_amount
----------------------------------------------------------
 10000000000   | PAYMENT01 |       12        |        18
 10000000000   | PAYMENT02 |       18        |         0
 10000000001   | PAYMENT02 |       10        |        15
 10000000001   | PAYMENT03 |        2        |        13
 10000000001   | PAYMENT04 |       13        |         0
 10000000002   | PAYMENT04 |       13        |         0
 10000000003   | PAYMENT04 |        4        |        14
 10000000003   | PAYMENT05 |       14        |         0
 10000000004   | PAYMENT05 |        1        |        11 

It would be nice if there is a solution with a "simple" select statement.

thx in advance for your time ...

Answer 1

Oracle Setup:

CREATE TABLE invoices ( i_id, invoice_number, creation_date, i_amount ) AS
SELECT 1, 100000000, DATE '2016-01-01', 30 FROM DUAL UNION ALL
SELECT 2, 100000001, DATE '2016-02-01', 25 FROM DUAL UNION ALL
SELECT 3, 100000002, DATE '2016-03-01', 13 FROM DUAL UNION ALL
SELECT 4, 100000003, DATE '2016-04-01', 18 FROM DUAL UNION ALL
SELECT 5, 100000004, DATE '2016-05-01', 12 FROM DUAL;

CREATE TABLE payments ( p_id, reference, received_date, p_amount ) AS
SELECT 1, 'PAYMENT01', DATE '2016-01-12', 12 FROM DUAL UNION ALL
SELECT 2, 'PAYMENT02', DATE '2016-01-13', 28 FROM DUAL UNION ALL
SELECT 3, 'PAYMENT03', DATE '2016-02-08',  2 FROM DUAL UNION ALL
SELECT 4, 'PAYMENT04', DATE '2016-02-23', 30 FROM DUAL UNION ALL
SELECT 5, 'PAYMENT05', DATE '2016-05-12', 15 FROM DUAL;

Query:

WITH total_invoices ( i_id, invoice_number, creation_date, i_amount, i_total ) AS (
  SELECT i.*,
         SUM( i_amount ) OVER ( ORDER BY creation_date, i_id )
  FROM   invoices i
),
total_payments ( p_id, reference, received_date, p_amount, p_total ) AS (
  SELECT p.*,
         SUM( p_amount ) OVER ( ORDER BY received_date, p_id )
  FROM   payments p
)
SELECT invoice_number,
       reference,
       LEAST( p_total, i_total )
         - GREATEST( p_total - p_amount, i_total - i_amount ) AS used_pay_amount,
       GREATEST( i_total - p_total, 0 ) AS open_inv_amount
FROM   total_invoices
       INNER JOIN
       total_payments
       ON (    i_total - i_amount < p_total
           AND i_total > p_total - p_amount );

Explanation:

The two sub-query factoring (WITH ... AS ()) clauses just add an extra virtual column to the invoices and payments tables with the cumulative sum of the invoice/payment amount.

You can associate a range with each invoice (or payment) as the cumulative amount owing (paid) before the invoice (payment) was placed and the cumulative amount owing (paid) after. The two tables can then be joined where there is an overlap of these ranges.

The open_inv_amount is the positive difference between the cumulative amount invoiced and the cumulative amount paid.

The used_pay_amount is slightly more complicated but you need to find the difference between the lower of the current cumulative invoice and payment totals and the higher of the previous cumulative invoice and payment totals.

Output:

INVOICE_NUMBER REFERENCE USED_PAY_AMOUNT OPEN_INV_AMOUNT
-------------- --------- --------------- ---------------
     100000000 PAYMENT01              12              18
     100000000 PAYMENT02              18               0
     100000001 PAYMENT02              10              15
     100000001 PAYMENT03               2              13
     100000001 PAYMENT04              13               0
     100000002 PAYMENT04              13               0
     100000003 PAYMENT04               4              14
     100000003 PAYMENT05              14               0
     100000004 PAYMENT05               1              11

Update:

Based on mathguy's method of using UNION to join the data, I came up with a different solution re-using some of my code.

WITH combined ( invoice_number, reference, i_amt, i_total, p_amt, p_total, total ) AS (
  SELECT invoice_number,
         NULL,
         i_amount,
         SUM( i_amount ) OVER ( ORDER BY creation_date, i_id ),
         NULL,
         NULL,
         SUM( i_amount ) OVER ( ORDER BY creation_date, i_id )
  FROM   invoices
  UNION ALL
  SELECT NULL,
         reference,
         NULL,
         NULL,
         p_amount,
         SUM( p_amount ) OVER ( ORDER BY received_date, p_id ),
         SUM( p_amount ) OVER ( ORDER BY received_date, p_id )
  FROM   payments
  ORDER BY 7,
           2 NULLS LAST,
           1 NULLS LAST
),
filled ( invoice_number, reference, i_prev, i_total, p_prev, p_total ) AS (
  SELECT FIRST_VALUE( invoice_number )  IGNORE NULLS OVER ( ORDER BY ROWNUM ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ),
         FIRST_VALUE( reference )       IGNORE NULLS OVER ( ORDER BY ROWNUM ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ),
         FIRST_VALUE( i_total - i_amt ) IGNORE NULLS OVER ( ORDER BY ROWNUM ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ),
         FIRST_VALUE( i_total )         IGNORE NULLS OVER ( ORDER BY ROWNUM ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ),
         FIRST_VALUE( p_total - p_amt ) IGNORE NULLS OVER ( ORDER BY ROWNUM ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ),
         COALESCE(
           p_total,
           LEAD( p_total ) IGNORE NULLS OVER ( ORDER BY ROWNUM ),
           LAG( p_total )  IGNORE NULLS OVER ( ORDER BY ROWNUM )
         )
   FROM  combined
),
vals ( invoice_number, reference, upa, oia, prev_invoice ) AS (
  SELECT invoice_number,
         reference,
         COALESCE( LEAST( p_total - i_total ) - GREATEST( p_prev, i_prev ), 0 ),
         GREATEST( i_total - p_total, 0 ),
         LAG( invoice_number ) OVER ( ORDER BY ROWNUM )
  FROM   filled 
)
SELECT invoice_number,
       reference,
       upa AS used_pay_amount,
       oia AS open_inv_amount
FROM   vals
WHERE  upa > 0
OR     ( reference IS NULL AND invoice_number <> prev_invoice AND oia > 0 );

Explanation:

The combined sub-query factoring clause joins the two tables with a UNION ALL and generates the cumulative totals for the amounts invoiced and paid. The final thing it does is order the rows by their ascending cumulative total (and if there are ties it will put the payments, in order created, before the invoices).

The filled sub-query factoring clause will fill the previously generated table so that if a value is null then it will take the value from the next non-null row (and if there is an invoice with no payments then it will find the total of the previous payments from the preceding rows).

The vals sub-query factoring clause applies the same calculations as my previous query (see above). It also adds the prev_invoice column to help identify invoices which are entirely unpaid.

The final SELECT takes the values and filters out the unnecessary rows.