Fill Null from Previous Non Null Value –(float or decimal)


 If (Object_Id('dbo.T1','U') is not null)
 Drop table dbo.T1;

CREATE TABLE T1
(
  id INT NOT NULL CONSTRAINT PK_T1 PRIMARY KEY,
  col1 INT NULL
  ,col_Float float  NULL
  ,col_Decimal Decimal(8,2)  NULL

);

 

INSERT INTO dbo.T1(id, col1, col_Decimal, col_Float) VALUES
( 2, NULL,NULL,NUll),
  ( 3,   10,8.45,3.33000258),
  ( 5,   -1,9.87,NULL),
  ( 7, NULL, NULL,NULL),
  (11, NULL, 5.55,NULL),
  (13,  -12, NULL,7.77123456),
  (17, NULL, NULL,NULL),
  (19, NULL, NULL,NULL),
  (23, 1759,1.23,0.000129);

  SELECT id, col_float, 
  CAST(Cast(SUBSTRING(
      MAX( CAST(id AS BINARY(4)) + Cast(Cast(col_float as decimal(38,17)) as  BINARY(38)) ) 
        OVER( ORDER BY id
              ROWS UNBOUNDED PRECEDING ),
      5, 38)
    AS  decimal(38,17) ) as float ) AS lastvalFloat

  

		, col_Decimal, CAST(
    SUBSTRING(
      MAX( CAST(id AS BINARY(4)) + CAST(col_Decimal AS BINARY(8)) )
        OVER( ORDER BY id
              ROWS UNBOUNDED PRECEDING ),
      5, 8)
    AS   Numeric(8,2)) AS lastvalDecimal


	-- 


FROM dbo.T1;

--Refer to: Itzik Ben-Gan
--http://sqlmag.com/t-sql/last-non-null-puzzle

 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/eab6bf17-1901-4b9b-8ab6-352d7b1519f2/lag-lead-functions-any-way-to-retrieve-the-1st-nonnull-values?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