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

Answer 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