Pull non blank from previous row

Question

I have data as below (first 3 columns). I would like to create the 4th column - newstatus.

Logic for the newstatus column is that

1. for each pk2
2. if column status is blank then pull value from the most recent row where status is either new or old.

Because of that row 3 gets value new which is from row 2 while row 9 gets value old from row 7. row 8 is ignored because it has value ignore

pk  status  pk2        newstatus
1           1   
2   new     1   
3           1          new
4   ignore  1   
5   ignore  1   
6           2   
7   old     2   
8   ignore  2   
9           2          old
10  new     2   

Answer 1

You can Use this script

WITH OuterT AS
(
SELECT *,ROW_NUMBER() OVER(PARTITION BY pk2 ORDER BY pk1) num FROM tbl
)
SELECT pk1,[Status],pk2,
(CASE WHEN num=1 or [Status] !='' THEN ''
WHEN num !=1 THEN
(SELECT TOP 1 innerT.[Status] FROM OuterT innerT WHERE innerT.pk2 =OuterT.pk2 and ([status] ='new' or [status] ='old') and num != 1 and innerT.pk1 < OuterT.pk1  ORDER BY pk1 DESC)
END) newstatus
FROM OuterT

How it works:

I used common_table_expression that its name is OuterT

WITH OuterT AS
(
SELECT *,ROW_NUMBER() OVER(PARTITION BY pk2 ORDER BY pk1) num FROM tbl
)

And I used ROW_NUMBER that its name is num

Inner result of OuterT is :

And then I used a CASE that Include your logic base on OuterT

(CASE WHEN num=1 or [Status] !='' THEN ''
WHEN num !=1 THEN
(SELECT TOP 1 innerT.[Status] FROM OuterT innerT WHERE innerT.pk2 =OuterT.pk2 and ([status] ='new' or [status] ='old') and num != 1 and innerT.pk1 < OuterT.pk1  ORDER BY pk1 DESC)
END) newstatus

And final result is :

Note: if you want to use script just replace “tbl” with your table name