Update the NULL Rows with Previous NOT NULL Rows

Question

I have a scenario where my table has 2 columns and one column has series of dates and another one has some values. Now i need to update the null values with the value corresponding to the most recent date like below.

I can't use LEAD and LAG functions as I am using SQL Server 2008R2.

Sample data is in the below.

DECLARE @Table TABLE
(
    Date_D DATE,
    Val INT
);

INSERT INTO @Table
(
    Date_D,
    Val
)
VALUES
('2019-06-15', 2),
('2019-06-16', NULL),
('2019-06-17', NULL),
('2019-06-18', 7),
('2019-06-19', 1),
('2019-06-20', 5),
('2019-06-21', NULL),
('2019-06-22', NULL),
('2019-06-23', NULL),
('2019-06-24', NULL),
('2019-06-25', 9),
('2019-06-26', 5),
('2019-06-27', 3),
('2019-06-28', 4),
('2019-06-29', NULL),
('2019-06-30', 1)



SELECT * FROM @Table

What have you tried so far? I suggest looking at Gaps and Islands if you haven't. Also, 2008(R2) has less than 4 weeks of support left, so you will want to look at getting that upgrade path finalised and deployed as soon as you can. Also, upgrading would solve your problem of not being able to use LEAD and LAG (not that you actually need them here). — Thom A
– Thom A ♦, Commented Jun 13, 2019 at 13:49

Zohar Peled · Accepted Answer · 2019-06-13 14:19:23Z

3

Update using a correlated subquery:

UPDATE T0
SET Val = (
    SELECT TOP 1 Val
    FROM @Table As T1
    WHERE Val IS NOT NULL
    AND T0.Date_D > T1.Date_D
    ORDER BY Date_D DESC
    ) 
FROM @Table As T0
WHERE Val IS NULL

Validate:

SELECT *
FROM @Table

Results:

Date_D          Val
15.06.2019      2
16.06.2019      2
17.06.2019      2
18.06.2019      7
19.06.2019      1
20.06.2019      5
21.06.2019      5
22.06.2019      5
23.06.2019      5
24.06.2019      5
25.06.2019      9
26.06.2019      5
27.06.2019      3
28.06.2019      4
29.06.2019      4
30.06.2019      1

answered Jun 13, 2019 at 14:19

Zohar Peled

82.9k11 gold badges79 silver badges134 bronze badges

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

3 Comments

Ram Over a year ago

What if the update needs to be done only if the data exists for last 4 months, else we should leave it as NULL

Zohar Peled Over a year ago

You add a condition to the subquery where clause - and T1.Date_D >= dateadd(month, -4. getdate)

Thom A Over a year ago

If this answers the question please mark it as the solution @Ram . You let other users know the answer was useful and you get reputation for taking to time to show your appreciation. You've never marked an answer you've received as the solution, so now is a good time to start. Thanks!

Tab Alleman · Accepted Answer · 2019-06-13 13:55:37Z

0

One way you can do this is with a sub-select, selecting the TOP 1 non-NULL value before the current Date_D.

answered Jun 13, 2019 at 13:55

Tab Alleman

31.8k7 gold badges41 silver badges54 bronze badges

Comments

Ilyes · Accepted Answer · 2019-06-13 19:09:10Z

-1

You can do as

SELECT Date_D, CASE WHEN Val IS NULL THEN
          (
            SELECT TOP 1 Val 
            FROM @Table 
            WHERE Val IS NOT NULL AND Date_D < T.Date_D 
            ORDER BY Date_D DESC
          ) ELSE Val END
FROM @Table T;

If you want to update the table then join it with the results.

For the UPDATE

;WITH CTE AS
(
  SELECT Date_D, CASE WHEN Val IS NULL THEN
            (
              SELECT TOP 1 Val 
              FROM @Table 
              WHERE Val IS NOT NULL AND Date_D < T.Date_D 
              ORDER BY Date_D DESC
            ) ELSE Val END Val
  FROM @Table T
)
UPDATE T
SET T.Val = CTE.Val
FROM @Table T INNER JOIN CTE
ON T.Date_D = CTE.Date_D
WHERE T.Val IS NULL; --You can also AND Date_D BETWEEN StartDate AND EndDate

See how it's working on a live demo

edited Jun 13, 2019 at 19:09

answered Jun 13, 2019 at 14:11

Ilyes

15k5 gold badges31 silver badges61 bronze badges

2 Comments

Ram Over a year ago

What if the update needs to be done only if the data exists for last 4 months, else we should leave it as NULL

Ilyes Over a year ago

If you feels that the answer is wrong or something, please feel free to leave a comment Mr. Downvoter.

Collectives™ on Stack Overflow

Update the NULL Rows with Previous NOT NULL Rows

3 Answers 3

3 Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related