SQL Server | UPDATE MISSING VALUES in sql | Sql Server Interview Part 38

For SQL Quiz-

Find Us On FaceBook-

Query---

IF EXISTS(SELECT 1 FROM SYS.TABLES WHERE NAME='UPDATEMISSINGNUMBER_P38')
BEGIN
DROP TABLE UPDATEMISSINGNUMBER_P38
END

CREATE TABLE UPDATEMISSINGNUMBER_P38
(
CARDNUM VARCHAR(10),
VALUE VARCHAR(100),
DATE DATETIME
)

INSERT INTO UPDATEMISSINGNUMBER_P38
SELECT 'C1',100,'2019-06-01'
UNION ALL
SELECT 'C1',110,'2019-07-01'
UNION ALL
SELECT 'C1',NULL,'2019-08-01'
UNION ALL
SELECT 'C2',1000,'2019-06-01'
UNION ALL
SELECT 'C2',NULL,'2019-07-01'
UNION ALL
SELECT 'C2',1150,'2019-08-01'
UNION ALL
SELECT 'C2',1150,'2019-09-01'
UNION ALL
SELECT 'C3',200,'2019-09-01'

select CARDNUM,isnull(value,lag(VALUE) over(partition by cardnum order by date)) as value,DATE
from UPDATEMISSINGNUMBER_P38

[prabhatgupta]

select cardnum, ifnull(value, lag(value)over( order by cardnum)), date from updat;

[piyushp]

SIMPLE CODE WITHOUT
LAG FUNCTION


WITH R AS
(
SELECT *, ROW_NUMBER() OVER ( PARTITION BY CARDNUM ORDER BY CARDNUM ) A FROM #B
)

SELECT CARDNUM, CASE WHEN VALUE IS NULL THEN (SELECT MAX(VALUE) FROM R T2 WHERE T2.A<T1.A AND T2.CARDNUM =T1.CARDNUM) ELSE VALUE END AS VALUE, DATE

FROM R T1

[vijay.s-llyq]

select *, max(value) over (partition by cardnum order by rmf) from
(select *, sum(case when value is null then 0 else 1 end) over (partition by cardnum order by rm) rmf from
(select *, row_number() over (partition by cardnum order by (select null)) rm from