SQL Puzzle | GET ISNULL(ISNULL( Next NON NULL Value, Previous NON NULL Value),0) Puzzle

In this puzzle you have to replace the null values with the non null values based on the following conditions.

1. Replace the null value with the next non null value if present.
2. If there is no next non null value then replace the null value with the previous non null value if present.
3. If there is no previous non null value and no next non null value then replace the null value with 0.
4. For more details please check the sample input and expected output.

Sample Input

Code Id Vals
1001 1 1000
1001 2 NULL
1001 3 NULL
1001 4 4000
1002 1 NULL
1002 2 2000
1002 3 3000
1002 4 NULL
1003 1 1210
1003 2 NULL
1004 1 NULL

Expected Output

code Id Vals
1001 1 1000
1001 2 4000
1001 3 4000
1001 4 4000
1002 1 2000
1002 2 2000
1002 3 3000
1002 4 3000
1003 1 1210
1003 2 1210
1004 1 0

Script

Use below script to create table and insert sample data into it.

--

CREATE TABLE FillNullData
(
     Code INT
    ,Id INT
    ,Vals INT
)

INSERT INTO FillNullData VALUES
(1001,1,1000),
(1001,2,NULL),
(1001,3,NULL),
(1001,4,4000),
(1002,1,NULL),
(1002,2,2000),
(1002,3,3000),
(1002,4,NULL),
(1003,1,1210),
(1003,2,NULL),
(1004,1,NULL)

SELECT * FROM FillNullData
GO


--

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

SOLUTION – 1

--


SELECT a.code,a.Id,COALESCE(a.Vals,z.Vals,z1.Vals,0) Vals 
FROM FillNullData a
OUTER APPLY 
(
	SELECT TOP 1 Vals
	FROM FillNullData b
	WHERE a.code = b.code
	AND b.Vals IS NOT NULL AND a.Id <b Id> b.Id
	ORDER BY Id DESC
)z1


--

Output-1

--                            

code        Id          Vals
----------- ----------- -----------
1001        1           1000
1001        2           4000
1001        3           4000
1001        4           4000
1002        1           2000
1002        2           2000
1002        3           3000
1002        4           3000
1003        1           1210
1003        2           1210
1004        1           0

(11 rows affected)

--

SOLUTION by Vaibhav Goel

--


;WITH CTE AS 
(
	SELECT * 
		   ,SUM(CASE WHEN vals IS NULL THEN 0 ELSE 1 END) OVER (PARTITION BY code ORDER BY ID DESC) g1
		   ,SUM(CASE WHEN vals IS NULL THEN 0 ELSE 1 END) OVER (PARTITION BY code ORDER BY ID ASC) g2
	FROM FillNullData
)
,CTE1 AS 
(
   SELECT *  
          ,MAX(vals) OVER (PARTITION BY code, g1) AS vals1
		  ,MAX(vals) OVER (PARTITION BY code, g2) AS vals2
   FROM CTE
)
SELECT code,Id,COALESCE(vals,Vals1,Vals2,0) Vals FROM CTE1
ORDER By code,Id


--

Output

--                            

code        Id          Vals
----------- ----------- -----------
1001        1           1000
1001        2           4000
1001        3           4000
1001        4           4000
1002        1           2000
1002        2           2000
1002        3           3000
1002        4           3000
1003        1           1210
1003        2           1210
1004        1           0
Warning: Null value is eliminated by an aggregate or other SET operation.

(11 rows affected)

--

Add a comment if you have any other or better solution in mind. I would love to learn it. We all need to learn.

Author – Pawan Khowal

Pawan is a SQL Server Expert. If you need any help in writing code/puzzle or training please email at – pawankkmr”AT”gmail.com. Meanwhile please go throgh the top pages from his blog.

Page Detail URL
☛ SQL Advance Puzzles https://msbiskills.com/tsql-puzzles-asked-in-interview-over-the-years/
☛ SQL Tricky Queries https://msbiskills.com/sql-puzzles-finding-outputs/
☛ SQL Server Perfomance tuning Articles https://msbiskills.com/sql-performance-tuning/
☛ SQL Server Articles https://msbiskills.com/t-sql/
☛ SQL Interview Questions & Answers https://msbiskills.com/sql-server-interview-questions/

My SQL Groups on Facebook:

1. If you like this post, you may want to join my SQL SERVER Interview Puzzles/Interview Questions on Facebook: https://www.facebook.com/groups/1430882120556342/

2. If you like this post, you may want to join my SQL Server Puzzles on Facebook:
https://www.facebook.com/groups/206594023090781/

My SQL Page on Facebook:

2. For all the updates you may follow my page -> https://www.facebook.com/MSBISkillscom-1602779883299222/

Enjoy !!! Keep Learning

Http://MsbiSkills.com

Advertisements