Compare 2 consecutive rows in same table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Compare 2 consecutive rows in same table

Author

Topic

nitin1309
Starting Member

3 Posts

Posted - 2012-06-07 : 13:47:45

Hello Everybody,

I need to compare 2 consecutive rows in the same table. If the data is missing in the second row, I should update it with the first row's data.

For Eg:
Row EmpID DATE PositionID EmpStatus EmpDept
------------------------------------------------------------------------
R1 21 2010-12-31 NULL TC 40
R2 21 2010-01-25 90156840101 NULL NULL
R3 21 2003-11-25 NULL AC NULL

First Iteration: Since Row1 EmpStatus = TC, I want to Update the EmpStatus on Row2 to TC (Since its NULL) and EmployeeDept to 40 on Row2.

Row EmpID DATE PositionID EmpStatus EmpDept
------------------------------------------------------------------------
R1 21 2010-12-31 NULL TC 40
R2 21 2010-01-25 90156840101 TC 40
R3 21 2003-11-25 NULL AC NULL

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

Row EmpID DATE PositionID EmpStatus EmpDept
------------------------------------------------------------------------
R1 21 2010-12-31 NULL TC 40
R2 21 2010-01-25 90156840101 TC 40
R3 21 2003-11-25 90156840101 AC 40

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.

Thanks a lot!

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-07 : 13:56:34

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.

nitin1309
Starting Member

3 Posts

Posted - 2012-06-07 : 14:09:25

Thanks nigel!
But I need to update this continuously in one pass. This code will have to go into a store proc and can only be run once.

Is there any other way, we can implement this.

Thanks!

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-06-14 : 06:43:15

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?

N 28° 33' 11.93148"
E 77° 14' 33.66384"

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-14 : 06:58:20

You can have a loop in a stored procedure.

==========================================
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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources