Database
Fan

## 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!

Tags (3)
2 REPLIES
Junior Contributor

## Re: Calculating min and max column values in Teradata

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 dtgroup by cust_name, grp`

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

Fan

## Re: Calculating min and max column values in Teradata

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