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
I am working on historical data load and I have to build records backwards.
Can anyone please tell me how to code this ? I wanna know if we can do this updates preferable without using cursors as I have a lot of employees in this table.
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.
Nigel's Query may be modified according to your requirement. What Logic should be applied if there are multiple rows containing NULL for EmpStatus and PositionId?
========================================== 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.
Compare 2 consecutive rows in same table
Posted - 06/07/2012 : 13:47:45
