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”.

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 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 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/