Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


T-SQL Query | [ The Previous Value(Non Null) Puzzle ]

  1. The Puzzle is simple. This puzzle is part two of the first puzzle I wrote.
  2. You have to find previous non null value from the value column.
  3. Please check out the sample input and expected output for details.

Sample Input

Id Value
12 NULL
31 100
52 -72
77 NULL
111 17
131 NULL
174 NULL
190 NULL
231 34

Expected Output

Id Value PrevNonNullValue
12 NULL NULL
31 100 100
52 -72 -72
77 NULL -72
111 17 17
131 NULL 17
174 NULL 17
190 NULL 17
231 34 34

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

Script

Use the below script to generate the source table and fill them up with the sample data.

—


CREATE TABLE PrevValue
(
   Id INT
  ,Value INT NULL
)
GO

INSERT INTO PrevValue VALUES
( 12, NULL),
( 31, 100),
( 52, -72),
( 77, NULL),
(111, 17),
(131, NULL),
(174, NULL),
(190, NULL),
(231, 34)

—

Update May 10 | Solutions – Pawan Kumar Khowal 


--

;WITH CTE AS
(
	SELECT * , ROW_NUMBER() OVER (ORDER BY %%Physloc%%) rnk FROM PrevValue
)
SELECT Id,Value
	,(SELECT TOP 1 Value from CTE d WHERE d.rnk = 
		(SELECT MAX(e.rnk) FROM CTE e WHERE e.rnk <= c.rnk AND e.Value IS NOT NULL)) 
PrevNonNullValue			
FROM CTE c

--

Add a comment if you have any other solution in mind. We all need to learn.

Keep Learning

Http://MSBISkills.com

Pawan Kumar Khowal