Calculating min and max column values in Teradata

Database

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!

2 REPLIES
Senior Apprentice

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 dt
group by cust_name, grp

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

Re: Calculating min and max column values in Teradata

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