Teradata Max Across Multiple Columns

Database
Fan

Teradata Max Across Multiple Columns

Hi,

I need some help with some code below; I'm relatively new to Teradata SQL Assistant (vs: 13) - sample data below:

Customer   Number      Date         Time           Seq         Agent            Phone         Update Date            Last Call             Last Time

A                123          10/1/13       11:51        5              AA                    1                12/1/01                  10/1/13               11:53

A                123          10/2/13       10:00        8              BB                    3                12/2/01                  10/2/13                9:00

B                456          11/29/13     1:15          10            AA                    1                9/8/07                    11/28/13              1:30

B                456          11/28/13     8:23           8             CC                    2                9/8/00                   11/29/13               8:50

I need to look across the columns and rows where the customer is the same and select the max date, the corresponding time to that date, the corresponding seq, agent, phone and update date. The update date needs to be <= the date and the last call and last time need to be >= the date.

So for customer A - answer would be date 10/1/13, time 11:51, seq 5, agent AA, phone 1, update date 12/1/01, last call 10/1/13 and last time 11:53.

Customer B answer would be date 11/28/13, time 8:23, seq 8, agent CC, phone 2, update date 9/8/00, last call 11/29/13 and last time 8:50

Thanks for your help!

Tags (3)
2 REPLIES
Enthusiast

Re: Teradata Max Across Multiple Columns

Hi,

I have not tested this but I hope it will work for you.

SELECT CUSTOMER, NUMBER, 

MAX(DATE) OVER(PARTITION BY NUMBER ORDER BY DATE DESC) AS MAX_DT,

MAX(TIME) OVER(PARTITION BY NUMBER, DATE ORDER BY DATE DESC) AS MAX_TIME,

MAX(Seq) OVER(PARTITION BY NUMBER,DATE,TIME ORDER BY DATE DESC) AS SEQ,

MAX(Agent) OVER(PARTITION BY NUMBER,DATE,TIME, SEQ ORDER BY DATE DESC) AS MAX_Agent,

MAX(PHone) OVER(PARTITION BY NUMBER,DATE,TIME, SEQ, Agent ORDER BY DATE DESC) AS MAX_Phone,

Update_date, Last_Call, Last_Time

FROm TABLE NAME

QUALIFY Update_date <= MAX_DATE AND Last_Call >= MAX_DATE

Let me know if there is any issue.

Khurram
Fan

Re: Teradata Max Across Multiple Columns

Thanks for the feedback. This does a pretty good job except where there are multiple Update_dates so for example:

Customer A has update_date 5/3/09, 5/10/09, 6/3/00 and 6/3/01 all of which happened prior to the max_date.

I tried to follow your logic and by creating:

max(update_dt) over(partition by number, date, time, seq, agent, phone order by date desc) as max_upddt

This didn't work though and I'm getting duplicate data.

I tried this code and it seems to work but I'm still in the validation stage:

qualify

row_number()

over(partition by number

order by date desc, time desc, seq desc, update_dt desc, last call asc, last time asc)=1)

As I said, I'm a novice so not sure if this will work or not.