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.