Fill Null Value in a column with extra requirement


http://social.msdn.microsoft.com/Forums/en-US/78715bd3-e309-44b1-afb8-f157124e4708/help-required-for-complex-query?forum=transactsql

CREATE TABLE tt (id INT,ord int,dt1 datetime, wd int)

INSERT INTO tt VALUES (101,'1','2011-12-20','484') 
INSERT INTO tt VALUES (101,'2','2011-02-12','444') 
INSERT INTO tt VALUES (101,'3','2011-02-12','444') 
INSERT INTO tt VALUES (101,'4','2011-02-14',NULL)

 
INSERT INTO tt VALUES (102,'1','2013-05-27','544') 
INSERT INTO tt VALUES (102,'2','2013-06-02','544') 
INSERT INTO tt VALUES (102,'3','2013-06-03',NULL) 
INSERT INTO tt VALUES (102,'4','2013-06-10',NULL) 
INSERT INTO tt VALUES (102,'5','2013-07-08',NULL) 
INSERT INTO tt VALUES (102,'6','2013-07-08','690') 
INSERT INTO tt VALUES (102,'7','2013-07-10','690')

;with mycte as
(
SELECT id,ord,dt1,wd, row_number()over(order by id, dt1,wd desc) rn
 
FROM tt A)

SELECT m.id,m.ord,m.dt1,m.wd,d2.wd2  FROM mycte m
OUTER APPLY (SELECT TOP 1 wd  FROM mycte
WHERE id=m.id and rn<= m.rn AND wd IS NOT NULL
ORDER BY rn desc) d2 (wd2 )
 
Order by id,ord

 

CREATE TABLE  tt_result (id INT,ord int,dt1 datetime, wd int)

INSERT INTO tt_result VALUES (101,'1','2011-12-20','484') 
INSERT INTO tt_result VALUES (101,'2','2011-02-12','444') 
INSERT INTO tt_result VALUES (101,'3','2011-02-12','444') 
INSERT INTO tt_result VALUES (101,'4','2011-02-14','444')

INSERT INTO tt_result VALUES (102,'1','2013-05-27','544') 
INSERT INTO tt_result VALUES (102,'2','2013-06-02','544') 
INSERT INTO tt_result VALUES (102,'3','2013-06-03','544') 
INSERT INTO tt_result VALUES (102,'4','2013-06-10','544') 
INSERT INTO tt_result VALUES (102,'5','2013-07-08','690') 
INSERT INTO tt_result VALUES (102,'6','2013-07-08','690') 
INSERT INTO tt_result VALUES (102,'7','2013-07-10','690')

 
select * from tt_result

 drop table tt_result,tt

 
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