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
