Skip to Main Content
  • Questions
  • Finding Rows that sums up to a given row using Match Recognize

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Naresh Kumar.

Asked: July 30, 2019 - 12:17 am UTC

Answered by: Chris Saxon - Last updated: July 30, 2019 - 10:42 am UTC

Category: SQL - Version: 12c

Viewed 100+ times

You Asked

I am trying to apply Match_Recognize to find all the payments that make up the invoice. More than one payment can be matched to one Invoice. I am not sure if this can be achieved using Match Recognize or if this is a right candidate for it.



 create table ae_misc_trxs (ae_num number, trx_id number, trx_type varchar2(10), amount number) 



/
INSERT ALL 
INTO ae_misc_trxs VALUES(123,1, 'INV', +10)
INTO ae_misc_trxs VALUES(123,2, 'PMT', -10)
INTO ae_misc_trxs VALUES(123,3, 'INV',+12)
INTO ae_misc_trxs VALUES(123,4, 'PMT', -10)
INTO ae_misc_trxs VALUES(123,5, 'PMT', -2)
INTO ae_misc_trxs VALUES(123,6, 'PMT', -8)
INTO ae_misc_trxs VALUES(123,7, 'PMT',-3)
INTO ae_misc_trxs VALUES(123,8, 'PMT',-2)
INTO ae_misc_trxs VALUES(123,9, 'INV', +13)
INTO ae_misc_trxs VALUES(123,10, 'INV', +60)
INTO ae_misc_trxs VALUES(123,11, 'PMT', -50)
INTO ae_misc_trxs VALUES(123,12, 'PMT', -5)
INTO ae_misc_trxs VALUES(123,13, 'PMT',-1)
SELECT NULL
  FROM DUAL;
/


So far, I've got this
SELECT * 
  FROM ae_misc_trxs A
MATCH_RECOGNIZE(
PARTITION BY ae_num ORDER BY trx_id
MEASURES inv.trx_id AS inv_trx
        ,pmt.trx_id AS pmt_trx
        ,MATCH_NUMBER() AS mno
         ALL ROWS PER MATCH
         AFTER MATCH SKIP TO LAST INV 
PATTERN (inv pmt+)        
DEFINE INV AS inv.trx_type='INV' AND (inv.amount + NEXT(pmt.amount) = 0)        
      ,PMT AS pmt.trx_type='PMT'      
);


The below rows should be selected

e.g. Trx_id 2 matches to trx_id 1
Trx_id 5,4 matches to Trx_id 3
6,7,8 matches to 9
11,12,13 DOES not MATCH to 10.


with LiveSQL Test Case:

and we said...

In the variable definition, you can check running totals with sum, count, etc.

So you can define the payment variable as:

pmt as pmt.trx_type='PMT' and sum ( -pmt.amount ) <= inv.amount


This keeps adding rows to the pattern until you hit the invoice total.

Making this trickier is the fact you've got payments that precede their invoices. To get around this I've defined another set of pattern variables, doing the check above on the (POST)INV variable.

Which gives something like:

select * 
from ae_misc_trxs a
match_recognize (
  partition by ae_num order by trx_id
  measures 
     inv.trx_id as inv_trx
    ,pmt.trx_id as pmt_trx
    ,match_number() as mno
    ,coalesce ( 
      sum ( -pmt.amount ),
      sum ( -prepmt.amount )
     ) pmt_tot
    ,case
       when final sum ( -pmt.amount ) = inv.amount or
            final sum ( -prepmt.amount ) = postinv.amount 
       then 'PAID'
       else 'OUTSTANDING'
     end paid
  all rows per match 
  pattern ( ( inv pmt+ ) | ( prepmt+ postinv ) )        
  define 
    inv as inv.trx_type='INV'
   ,pmt as pmt.trx_type='PMT' and sum ( -pmt.amount ) <= inv.amount
   ,prepmt as trx_type='PMT' 
   ,postinv as trx_type='INV' and sum ( -prepmt.amount ) <= postinv.amount
);

AE_NUM    TRX_ID    INV_TRX    PMT_TRX    MNO    PMT_TOT    PAID           TRX_TYPE    AMOUNT   
      123         1          1     <null>      1     <null> PAID           INV                10 
      123         2          1          2      1         10 PAID           PMT               -10 
      123         3          3     <null>      2     <null> PAID           INV                12 
      123         4          3          4      2         10 PAID           PMT               -10 
      123         5          3          5      2         12 PAID           PMT                -2 
      123         6     <null>     <null>      3          8 OUTSTANDING    PMT                -8 
      123         7     <null>     <null>      3         11 OUTSTANDING    PMT                -3 
      123         8     <null>     <null>      3         13 OUTSTANDING    PMT                -2 
      123         9     <null>     <null>      3         13 PAID           INV                13 
      123        10         10     <null>      4     <null> OUTSTANDING    INV                60 
      123        11         10         11      4         50 OUTSTANDING    PMT               -50 
      123        12         10         12      4         55 OUTSTANDING    PMT                -5 
      123        13         10         13      4         56 OUTSTANDING    PMT                -1


You can then filter the results to the paid invoices in a where clause:

select * 
from ae_misc_trxs a
match_recognize (
  partition by ae_num order by trx_id
  measures 
     inv.trx_id as inv_trx
    ,pmt.trx_id as pmt_trx
    ,match_number() as mno
    ,coalesce ( 
      sum ( -pmt.amount ),
      sum ( -prepmt.amount )
     ) pmt_tot
    ,case
       when final sum ( -pmt.amount ) = inv.amount or
            final sum ( -prepmt.amount ) = postinv.amount
       then 'PAID'
       else 'OUTSTANDING'
     end paid
  all rows per match 
  pattern ( ( inv pmt+ ) | ( prepmt+ postinv ) )        
  define 
    inv as inv.trx_type='INV'
   ,pmt as pmt.trx_type='PMT' and sum ( -pmt.amount ) <= inv.amount
   ,prepmt as trx_type='PMT' 
   ,postinv as trx_type='INV' and sum ( -prepmt.amount ) <= postinv.amount
)
where  paid = 'PAID' and trx_type = 'INV';

AE_NUM    TRX_ID    INV_TRX    PMT_TRX    MNO    PMT_TOT    PAID    TRX_TYPE    AMOUNT   
      123         1          1     <null>      1     <null> PAID    INV                10 
      123         3          3     <null>      2     <null> PAID    INV                12 
      123         9     <null>     <null>      3         13 PAID    INV                13 


Note this does assume that all payments and invoice totals add up neatly! If this isn't true (e.g. you have no payments between issuing two invoices), things get complicated quickly...

Is this answer out of date? If it is, please let us know via a Review

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.