PzValium PzValium - 6 months ago 20
SQL Question

Oracle SQL sum up values till another value is reached

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 ...

MT0 MT0
Answer

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
Comments