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!
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.
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:
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.