Calculate based on the 2 consecutive rows from current row

Database
Highlighted
Enthusiast

Calculate based on the 2 consecutive rows from current row

I have a Table withEmp_ID,weeklydate andState_Num. Based on the change in values of state_Num I need to calculate the change_indicator. I would need to partition by Emp_ID and check the statenum of weekly date in asc order, when there is a change of state_num in the next week then the indicator should be 1. But when the state num changes from 1 number to another and back to same number then it is a glitch so it should not be updated to 1. (For ex: for Emp_ID 1 the state num changed from 1234 to 5678 on 1/18/16 hence 1. But on 2/1/16 the state num changed to 123 and then back to 5678 hence its a glitch and the indicator should be 1). I should check the current row with the next 2 consecutive rows only.

 

Please help me with a Teradata SQL and I use Teradata 14.

 

Emp_IDWeeklydatestate_Num
11/11/161234
11/18/161234
11/25/165678
12/1/16123
12/8/165678
12/15/165678
211/26/185556
212/3/185556
212/10/185556
312/17/18123
312/24/185556
312/31/185556

 

 

expected Result:

Emp_IDWeeklydatestate_Numchange_Indicator
11/11/1612340
11/18/1612341
11/25/1656780
12/1/161230
12/8/1656780
12/15/1656780
211/26/1855560
212/3/1855560
212/10/1855560
312/17/181230
312/24/1855560
312/31/1855560