CODEWITHSUNDEEP
sqlteradata
Moosa
Moosa

Reputation: 3216

SQL - Output column based on latest date

I have a table like the below. I have the first 4 columns. The last 2 are the ones I want to add. I'm on Teradata.

custid  channel activity_date   close_date  lastchannel days_before_close
11      email   2-Jan-16        3-Feb-16    meeting     28
11      call    3-Jan-16        3-Feb-16    meeting     28
11      mail    4-Jan-16        3-Feb-16    meeting     28
11      email   5-Jan-16        3-Feb-16    meeting     28
11      meeting 6-Jan-16        3-Feb-16    meeting     28

1) lastchannel: I want to output the channel name for the max activity date. So in the example above, I want the new column to say "meeting" in all rows.

2) the days between the close date and the last activity date: In this case, the days between Feb 3 and Jan 6 is 28.

I tried the below but I get a error saying I need a succeeds or precedes statement somewhere.

first_value(channel) over (partition by cust_id, activity_date order by activity_date desc) as lastchannel

Upvotes: 1

Views: 237

Answers (2)

dnoeth
dnoeth

Reputation: 60462

This is the same as Gordon's, based on your comment you might want this:

first_value(case when activity_date <= close_date then channel end ignore nulls) 
over (partition by cust_id
      order by activity_date desc) as lastchannel

And the 2nd is

close_date - max(activity_date) over (partition by cust_id) as days_before_close

Based on your comment:

close_date - max(case when activity_date <= close_date then activity_date end)
             over (partition by cust_id) as days_before_close

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269483

I would expect this logic to work:

first_value(channel) over (partition by cust_id
                           order by activity_date desc
                          ) as lastchannel

Perhaps you need an explicit windowing clause:

first_value(channel) over (partition by cust_id
                           order by activity_date desc
                           rows between unbounded preceding and current row
                          ) as lastchannel

Or even:

first_value(channel) over (partition by cust_id
                           order by activity_date desc
                           rows between current row and current row
                          ) as lastchannel

If this version works.

Upvotes: 1

Related Questions