My dataset looks like this in teradata:
╔═══════════╦══════════╦══════╗
║ studentid ║ date ║ days ║
╠═══════════╬══════════╬══════╣
║ 1000 ║ 2/1/2017 ║ 25 ║
║ 1000 ║ 3/8/2017 ║ 30 ║
║ 1000 ║ 4/4/2017 ║ 80 ║
║ 1000 ║ 5/1/2017 ║ 81 ║
║ 1001 ║ 1/1/2017 ║ 60 ║
║ 1001 ║ 2/1/2017 ║ 20 ║
║ 1001 ║ 4/1/2017 ║ 81 ║
╚═══════════╩══════════╩══════╝
I would like to have a new column (flag) that should indicate 1 on rows if the two recent dates have either 80 or 81. If not 0.
For Student 1001, it should be 0 for all rows because the last two dates are not 80 or 81. it needs to take the last two dates. even though 1001 has 81, the 2nd last date has 20, so the flag needs to be 0 for both
Desired Output :
╔═══════════╦══════════╦══════╦══════╗
║ studentid ║ date ║ days ║ flag ║
╠═══════════╬══════════╬══════╬══════╣
║ 1000 ║ 2/1/2017 ║ 25 ║ 0 ║
║ 1000 ║ 3/8/2017 ║ 30 ║ 0 ║
║ 1000 ║ 4/4/2017 ║ 80 ║ 1 ║
║ 1000 ║ 5/1/2017 ║ 81 ║ 1 ║
║ 1001 ║ 1/1/2017 ║ 60 ║ 0 ║
║ 1001 ║ 2/1/2017 ║ 20 ║ 0 ║
║ 1001 ║ 4/1/2017 ║ 81 ║ 0 ║
╚═══════════╩══════════╩══════╩══════╝
Assign row numbers with row_number and then get the min and max value of the last 2 rows per studentid. Thereafter, check the conditions with a case expression to assign flag.
select studentid,dt,days
,case when rnum in (1,2) and max_days_latest_2 in (80,81) and min_days_latest_2 in (80,81) then 1 else 0 end as flag
from (select t.*
,max(case when rnum in (1,2) then days end) over(partition by studentid) as max_days_latest_2
,min(case when rnum in (1,2) then days end) over(partition by studentid) as min_days_latest_2
from (select t.*,row_number() over(partition by studentid order by dt desc) as rnum
from tbl t
) t
) t