Fill Null Values Beased on Previous Non-Null Value and Asssign Sequence


  


 
DECLARE @TBL TABLE (RowNum INT, DataId int, RowOrder DECIMAL(18,2) NULL)
INSERT INTO @TBL VALUES
(1,    105508,       1.00),
(2,    105717,       NULL),
(3,    105718,       NULL),
(4,    105509,       2.00),
(5,    105510,       3.00),
(6,    105514,       NULL),
(7,    105513,       4.00),
(8,    105719,       NULL),
(9,    105718,       NULL),
(10,105718,   NULL)

;WITH mycte0 AS
(SELECT RowNum ,DataId,RowOrder
   FROM @TBL
  WHERE RowOrder IS NOT NULL )
  ,mycte as (
  Select RowNum, DataId, Coalesce(RowOrder, (SELECT MAX(m.RowOrder)
                FROM mycte0 m
               WHERE m.RowNum <= s.RowNum))  RowOrder
 FROM @TBL AS s)

 ,mycte2 as (
SELECT  RowNum, DataId, RowOrder
 ,RowOrder + Row_number() Over(Partition by RowOrder  Order by  RowNum) / 100.0 newRowOrder

FROM mycte  )

Merge @TBL as t
Using mycte2 as src on t.RowNum = src.RowNum 
When matched then 
Update Set 
RowOrder=src.newRowOrder;

 Select * from @TBL;

--Option 2
DECLARE @TBL2 TABLE (RowNum INT, DataId int, RowOrder DECIMAL(18,2) NULL)
INSERT INTO @TBL2 VALUES
(1,    105508,       1.00),
(2,    105717,       NULL),
(3,    105718,       NULL),
(4,    105509,       2.00),
(5,    105510,       3.00),
(6,    105514,       NULL),
(7,    105513,       4.00),
(8,    105719,       NULL),
(9,    105718,       NULL),
(10,105718,   NULL)
 
 ;with mycte as (
SELECT RowNum,DataId,RowOrder,
CAST(SUBSTRING(MAX( CAST(DataId AS BINARY(4)) + CAST(RowOrder AS BINARY(8)) )
OVER( ORDER BY RowNum ASC ROWS UNBOUNDED PRECEDING ),5,8) AS Decimal(18,2) ) lastNonNullval
 
  FROM @TBL2

   ) 
,mycte2 as (

Select RowNum,DataId,RowOrder
,lastNonNullval+(row_number()Over(Partition by lastNonNullval Order by RowNum )-1)/100.0 as newRowOrder 
From mycte
 )
  Update mycte2 
 set RowOrder=newRowOrder;


 Select * from @TBL2
 
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