Accenture SQL Interview Questions | SQL Interview Preparation | Previous not null value |

TCS interview questions and solutions:

Find Us On YouTube- “Subscribe Channel to watch Database related videos” https://www.youtube.com/channel/UCZA_5vGtSpZu86VBDdSnSag

For SQL Quiz-

Find Us On FaceBook-
https://www.facebook.com/SS-Unitech-187705388674384/

IF EXISTS(SELECT 1 FROM SYS.tables WHERE NAME=’prev_null_val’)
BEGIN
DROP TABLE prev_null_val
END

CREATE TABLE prev_null_val
(
SEQ INT,
ID INT,
NAME VARCHAR(50)
)

INSERT INTO prev_null_val VALUES (1,10,’A’),(2,NULL,’B’),(3,20,’C’),(4,null,’D’),(5,NULL,’E’),(6,30,’F’)

;WITH CTE AS
(
SELECT SEQ,ID,NAME,SUM(CASE WHEN ID IS NULL THEN 0 ELSE 1 END) OVER (ORDER BY SEQ) AS VAL_P
FROM prev_null_val
)
SELECT SEQ,ID,NAME,FIRST_VALUE(ID) OVER (PARTITION BY VAL_P ORDER BY SEQ) AS NEW_ID
FROM CTE

source