CODEWITHSUNDEEP
sqlteradatawindow-functionsgaps-and-islands
captain725
captain725

Reputation: 5

Teradata SQL -Min Max transaction dates from Rows

Tried Qualify row_Number () and Qualify Min & Max functions but still not able to get range of dates for transaction. See data structure below

enter image description here

Need help for the following output

enter image description here

Thank you in advance

Upvotes: 0

Views: 644

Answers (1)

dnoeth
dnoeth

Reputation: 60462

You need to find the groups of consecutive dates first. There are several ways to do this, in your case the best should is based on comparing a sequence to another sequence with gaps in it:

with cte as
 (
   select t.*
      -- consecutive numbers = sequence without gaps
     ,row_number()
      over (partition by location, cust#, cust_type -- ??
            order by transaction_date) as rn
      -- consecutive numbers as long as there's no missing date = sequence with gaps
     ,(transaction_date - date '0001-01-01') as rn2

      -- assign a common (but meaningless) value to consecutive dates,
      -- value changes when there's a gap
     ,rn2 - rn as grp
   from tab as t
 )
select location, cust#, cust_type -- ??
  ,min(transaction_date), max(transaction_date)
  ,min(amount), max(amount)
from cte
 -- add the calculated "grp" to GROUP BY 
group by location, cust#, cust_type, grp

The columns used for PARTITION BY/GROUP BY depend on your rules.

Upvotes: 1

Related Questions