Assign values based on dates by group

General

Assign values based on dates by group

I have a table looks like this:

groupID  Date

1   2016-01-03

1   2016-01-02

1   2016-01-01

2   2016-01-04

2   2016-01-05

2   2016-01-06

I would like to assign "initial" to the oldest date, "recent" to the most recent date, and the others "interim" according to groupID (1,2).

Any suggestion on how to do it in teradata SQL will be appreciated, thanks!

1 REPLY
Senior Apprentice

Re: Assign values based on dates by group

For each groupid?

case datecol
when min(datecol) over (partition by groupid) then 'initial'
when max(datecol) over (partition by groupid) then 'recent'
else 'interim'
end