Compare current row values with in last five days and mark if match found

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
Enthusiast

Compare current row values with in last five days and mark if match found

Hi All,

 

  I am new user of teradata and unable to solve this teradata query. Please see input and output tables below and if you can help with some solutions .

 

Input Table :  amt column has incoming and outgoing transactions
key1key2txn_datecodeamt 
1111/02/201710100 
1111/02/201710100 
2211/02/201710200 
1114/02/201710100 
2214/02/201720-200 
1116/02/201720-100 
      
      
Mark rows with 1 as indicator where outgoing(-ve) amt 
has corresponding incoming(+ve) amount with in 5 days
There are more outgoing records than incoming 
Output Table    
key1key2txn_datecodeamtIndicator
1111/02/2017101001
1114/02/201710100 
1116/02/201720-1001
1111/02/201710100 
2211/02/2017102001
2214/02/201710-2001

 

Thanks in advance

 

 

  • Teradata
Tags (1)

Accepted Solutions
Senior Apprentice

Re: Compare current row values with in last five days and mark if match found

That's is a tricky task. 

This might be what you want, maybe it can be simplified, but I doubt you will be able to get the expedcted result with less than two Stats-steps in Explain:

SELECT dt.*,
   CASE
      WHEN (rn = 1 AND amt > 0 ) -- there's a matching withdrawal
        OR (rn>1 AND amt <0)     -- there's a matching deposit
      THEN 1
   END AS flag
FROM 
 (
   SELECT dt.*,
      -- assign 1 to the first matching deposit before a withdrawal
      Sum(CASE WHEN outdate -5 <= txn_date THEN 1 ELSE 0 END) -- deposit must be within 5 days before withdrawal
      Over (PARTITION BY key1, key2, grp
            ORDER BY txn_date, amt DESC
            ROWS Unbounded Preceding) AS rn
   FROM
    (
      SELECT tab.*,
         Sum(CASE WHEN amt < 0 THEN 1 ELSE 0 END) -- whenever there's a withdrawal create a new group 
         Over (PARTITION BY key1, key2, Abs(amt)  -- only rows with the same amount, either positive or negative
               ORDER BY txn_date DESC, amt
               ROWS Unbounded Preceding) AS grp,
         Min(CASE WHEN amt < 0 THEN txn_date END) -- next withdrawal date
         Over (PARTITION BY key1, key2, Abs(amt)
               ORDER BY txn_date DESC, amt 
               ROWS Unbounded Preceding) AS outdate                    
      FROM tab
    ) AS dt
 ) AS dt
ORDER BY  key1, key2, grp, txn_date

 

1 ACCEPTED SOLUTION
8 REPLIES
Senior Apprentice

Re: Compare current row values with in last five days and mark if match found

Why does 

16/02/201720-100

match

11/02/201710100

and not 

14/02/201710100

?

 

 

Enthusiast

Re: Compare current row values with in last five days and mark if match found

Solution has an inherit assumption that first return of equal amount is for first credit gone into account. I hope this makes sense.

Senior Apprentice

Re: Compare current row values with in last five days and mark if match found

That's is a tricky task. 

This might be what you want, maybe it can be simplified, but I doubt you will be able to get the expedcted result with less than two Stats-steps in Explain:

SELECT dt.*,
   CASE
      WHEN (rn = 1 AND amt > 0 ) -- there's a matching withdrawal
        OR (rn>1 AND amt <0)     -- there's a matching deposit
      THEN 1
   END AS flag
FROM 
 (
   SELECT dt.*,
      -- assign 1 to the first matching deposit before a withdrawal
      Sum(CASE WHEN outdate -5 <= txn_date THEN 1 ELSE 0 END) -- deposit must be within 5 days before withdrawal
      Over (PARTITION BY key1, key2, grp
            ORDER BY txn_date, amt DESC
            ROWS Unbounded Preceding) AS rn
   FROM
    (
      SELECT tab.*,
         Sum(CASE WHEN amt < 0 THEN 1 ELSE 0 END) -- whenever there's a withdrawal create a new group 
         Over (PARTITION BY key1, key2, Abs(amt)  -- only rows with the same amount, either positive or negative
               ORDER BY txn_date DESC, amt
               ROWS Unbounded Preceding) AS grp,
         Min(CASE WHEN amt < 0 THEN txn_date END) -- next withdrawal date
         Over (PARTITION BY key1, key2, Abs(amt)
               ORDER BY txn_date DESC, amt 
               ROWS Unbounded Preceding) AS outdate                    
      FROM tab
    ) AS dt
 ) AS dt
ORDER BY  key1, key2, grp, txn_date

 

Enthusiast

Re: Compare current row values with in last five days and mark if match found

Hi Dnoeth,      Many thanks for providing the solution. It is very close to my requirement however when I am using below data. One of the rows with amount 110 is not matching. I am unable to understand please advise on fix . key1    key2    txn_date    code    amt    1    1    10/02/2017    10    100    1    1    13/02/2017    10    110    Not matching1    1    10/02/2017    10    100    1    1    13/02/2017    10    100    1    1    15/02/2017    20    -100    1    1    14/02/2017    20    -100    1    1    16/02/2017    20    -110    Not matching1    1    15/02/2017    10    300    1    1    10/02/2017    10    200    1    1    13/02/2017    20    -200     Many Thanks, Bucci
Enthusiast

Re: Compare current row values with in last five days and mark if match found

Calculation for 'next withdrawal date' is creating issues. As in all cases for amt < 0 following statement will be 1 . CASE WHEN outdate -7 <= txn_date THEN 1 ELSE 0 END
Enthusiast

Re: Compare current row values with in last five days and mark if match found

Hi , One more point to add is that if we have amount withdrawal and no corresponding deposit as a record in table still it will be flagged as 1 (corresponding match) in current solution. Please advise on this issue. Thanks, B
Senior Apprentice

Re: Compare current row values with in last five days and mark if match found

My approach is flawed, it's probably not possible to handle this complex scenario using simple OLAP functions.

You need some kind of recursive approach instead.

Enthusiast

Re: Compare current row values with in last five days and mark if match found

Thanks for the clarification on issue. Can you suggest some solution using recursive approach. It will be helpful.