All Forums Database
raindew12 2 posts Joined 07/16
31 Jul 2016
Calculating min and max column values in Teradata

I've a data in the following format  -
cust_name , month, isAccountActive
A, jan-15, 1
A, feb-15, 1
A, mar-15, 0
A, apr-15 , 1
A,  may-15, 0
B, jan -15, 0
B, feb-15,1
B, mar-15,1
B, apr-15, 1
 
My output should look like follows-
cust_name, start_date, Account_active_months
A, jan-15, jan-15 to feb-15
A, apr-15, apr-15 to apr-15
B, feb-15, feb-15 to apr-15
 
Basically I need to find out the start date when the customer's account became active and duration for which it was active. It has to be grouped by each customer as shown above. How do I achieve this in teradata?
 
Any help is appreciated. Thanks!
 

dnoeth 4628 posts Joined 11/04
01 Aug 2016

You want to combine consecutive rows, this is usually done by assigning a group number using an OLAP function followed by aggregation:

select cust_name,
   min(month),
   max(month)
from 
 (
   select cust_name, month, isAccountActive,
        -- Cumulative sum asssigning a new number whenever account is inactive
       sum(case when isAccountActive = 1 then 0 else 1 end)
       over (partition by cust_name 
             order by month
             rows unbounded preceding) as grp
   from tab
        -- now remove the inactive rows
   qualify isAccountActive = 1
 ) as dt
group by cust_name, grp

Of course this assumes that month is actually a date or sorts the correct way.

Dieter

raindew12 2 posts Joined 07/16
01 Aug 2016

Awesome that worked! Thank you Dieter!! :-)

You must sign in to leave a comment.