Fill Null From Previous Non-NULL Value in A Column



CREATE  TABLE Table1 (Prod_Order int, Datettime datetime, Datavalue Decimal(18,7))

INSERT INTO Table1 values('106', '2015-02-20 09:00:00.000' , 1010.3499756)
INSERT INTO Table1 values('106', '2015-02-20 10:00:00.000',    null)
INSERT INTO Table1 values('106', '2015-02-20 11:00:00.000' ,  3113.1999512)
INSERT INTO Table1 values('106', '2015-02-20 12:00:00.000' ,           null)
INSERT INTO Table1 values('106', '2015-02-20 13:00:00.000' ,           null)
INSERT INTO Table1 values('106', '2015-02-20 14:00:00.000' ,            null)
INSERT INTO Table1 values('106', '2015-02-20 15:00:00.000' ,           null)
INSERT INTO Table1 values('106', '2015-02-20 16:00:00.000' ,  5219.0998536)


--select * from Table1
 
SELECT Prod_Order, /*Datavalue, */
Datettime,
CAST(SUBSTRING(MAX( CAST(Datettime AS BINARY(32)) + CAST(Datavalue AS BINARY(12)) )
OVER( ORDER BY Datettime ASC ROWS UNBOUNDED PRECEDING ),33,12) AS Decimal(18,7) ) lastNonNullval

  
FROM Table1
 

drop table Table1
/*



*/

--Check this link about this solution:
--Itzik Ben-Gan
--http://sqlmag.com/t-sql/last-non-null-puzzle

--
/*
From your question, you need to use right data type for table columns and I made some changes to provide the solution posted.

1.Use the right data type for your table columns (your have datetime data not datetime2 and Datavalue is not varchar(200));
2. An empty string '' is not the same as a null value and null is null and it is an important concept.

By the way, thanks for providing your table structure and sample data.

*/

 

https://social.msdn.microsoft.com/Forums/en-US/b3167796-5f96-4188-9230-35076f5cb44c/sql-to-copy-data-from-previous-non-null-rows?forum=transactsql

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