How to Fill Column Null Values from Previous Non-null Value or Next if No Previous Exists (2)




 create table test2 (name varchar(50), person_id int,value int)
insert into test2 values ('alice',    12 , null)  ,    
('christa', 13,  10),
('hally',14,   null),
('jack',  18,   12),
('raj', 20,   null),
('jane',22,    13),
('jodie',  25, null)
 
 
  SELECT name, person_id, newValue=ISNULL(MAX(value) OVER (PARTITION BY c), MAX(value) OVER (PARTITION BY c1) )

FROM
(
    SELECT name, person_id,value
        ,c=COUNT(value) OVER (ORDER BY person_id desc)
		,c1=COUNT(value) OVER (ORDER BY person_id )
    FROM test2
) a
ORDER BY person_id;

 
--;with mycte as
--(
--SELECT name,person_id,value, row_number()over(order by person_id ) rn
--FROM test2 A)
 
--SELECT m.name,m.person_id, ISNULL(d.value,e.value) AS value
--FROM   mycte m
--       OUTER APPLY (SELECT TOP 1 *  FROM   mycte
--                    WHERE  rn <= m.rn AND value IS NOT NULL
--                    ORDER  BY rn DESC ) d
--       OUTER APPLY (SELECT TOP 1 *  FROM   mycte
--                    WHERE  Value IS NOT NULL
--                    ORDER  BY rn) e
--ORDER  BY m.person_id 
 



 
drop table test2
 


 
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s