SQL Server replace NULL value with last value from previous row

Question

I'd like to replace NULL value for each code group using the value before the first Null:

code     amount       date
-------------------------------
 A        1500     2013-02-03
 A        1800     2013-03-05
 A        1300        NULL
 A        1300        NULL
 B        1300     2013-05-03
 B        1300     2013-06-01
 B        1300     2013-07-02
 B        1600        NULL
 B        1300        NULL
 C        1200     2014-07-02
 C        1500     2014-08-05
 C        1700        NULL
 C        1100        NULL
 C        1300        NULL

In this table, I want to replace NULL value for the date for each "code" with last date value for that code. e.g. date for code A should look like this and same for the others.

code     amount       date
-----------------------------
 A        1500     2013-02-03
 A        1800     2013-03-05
 A        1300     2013-03-05
 A        1300     2013-03-05

I know there are other similar posts for this question, but none of them give me the result above. I guess because selecting last value for each specific code is a bit tricky.

Something like UPDATE T SET [date] = U.[date] FROM myTable T CROSS APPLY (SELECT MAX([date]) FROM myTable WHERE code = T.code) U([date]) WHERE T.[date] IS NULL? — ZLK
– ZLK, Commented Oct 4, 2016 at 4:26
Do you want to UPDATE NULL values in the original table, or write a SELECT query that returns the result set without NULLs? — Vladimir Baranov
– Vladimir Baranov, Commented Oct 4, 2016 at 4:29

artm · Accepted Answer · 2016-10-04 04:29:25Z

2

How about?

UPDATE mytable
SET date = date2
FROM mytable o
  CROSS APPLY
    (SELECT MAX(date) date2 FROM mytable i WHERE i.id=o.id group by id) ii
where o.date is null

answered Oct 4, 2016 at 4:29

artm

8,5943 gold badges28 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Server replace NULL value with last value from previous row

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related