Second Iteration: Since PositionID is NULL on Row3, I wanna update Row3 with the PositionID of Row2. Since Row2 now has EmpStatus = TC, I want to compare the row2 and row3 data. Since Row3 has a new value, I wanna retain the new value = AC. But at the same time I wanna update the value of EmpDept of Row3 = 40 since its NULL
with cte as ( select *, row_number() over (order by EmpID, DATE ) frrom tbl ) update cte set PositionID = t2.PositionID from cte join cte t2 on cte.seq = t2.seq+1 where cte.PositionID is null and t2.PositionID is not null
Might have to run it a few times if you have consecutive rows with nulls.
========================================== Cursors are useful if you don't know sql. SSIS can be used in a similar way. Beer is not cold and it isn't fizzy.