Fill Null Value in a Column From Either Above or Below Non-Null Value



CREATE TABLE [dbo].[tariff_ratebrek](
	[break_seq_num] [int] NULL,
	[break_amount] [float] NULL,
	[break_unit] [float] NULL,
	[break_point] [float] NULL
) 
 

INSERT INTO tariff_ratebrek
VALUES (0,400,0,0),
(1,100,0,2),
(2,200,1,3),
(3,150,1,5),
(4,250,1,6),
(5,400,1,7),
(6,250,1,10),
(7,220,1,28),
(8,200,1,55),
(9,150,1,9999)

;WITH Nums (num) AS (
SELECT 0 as num
UNION ALL SELECT num+1 as n
FROM Nums Where num<55)

--Num2 (num) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
--Nums (num) AS (SELECT ROW_NUMBER() OVER(ORDER BY num)-1 FROM Num2 where num<50)



,mycte as
(
select [break_seq_num], break_amount,  [break_unit], t.break_point, d.num ,  row_number()over(order by num ) rn
from [tariff_ratebrek] t 
RIGHT Join  Nums d 
 on t.break_point=d.num
)

 
Select break_unit, bu,[break_point], bp,  ba, break_amount, m.num from mycte m
Cross apply (select top 1 break_unit from mycte m1 WHERE m1.rn<=m.rn and break_unit is not null Order by rn DESC) d1(bu) 
--bp is filled down with non-null value in  break_point from above
Cross apply (select top 1 [break_point] from mycte m2 WHERE m2.rn<=m.rn and break_point is not null   Order by rn DESC) d2(bp) 

--ba is filled down with non-null value in break_amount from below
Cross APPLY (SELECT TOP 1 break_amount FROM   mycte m3 WHERE  m3.rn>= m.rn AND break_amount IS NOT NULL   ORDER  BY rn  ) d3( ba)

--option(maxrecursion 100)

drop table [tariff_ratebrek]
 


 
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