Fill out missing dates based on the minimum date [duplicate]

Question

I have a table with dates (the dates will always be ordered) and a value for example:

       Dates           Value
     2007-01-01         10
     2007-01-02         Null
     2007-01-03         Null
     2007-01-04         Null
     2007-01-05         20
     2007-01-06         Null
     2007-01-07         40

I want to replace the 'Null' values with the value from the earliest date that contains a value, for example:

     Dates             Value
     2007-01-01         10
     2007-01-02         10
     2007-01-03         10
     2007-01-04         10
     2007-01-05         20
     2007-01-06         20
     2007-01-07         40

Answer 1

As an answer :

CREATE TABLE A_TABLE
(A_DATE     DATE,
 A_VALUE    INT);

INSERT INTO A_TABLE VALUES
(' 2007-01-01', 10),
(' 2007-01-02', Null),
(' 2007-01-03', Null),
(' 2007-01-04', Null),
(' 2007-01-05', 20),
(' 2007-01-06', Null),
(' 2007-01-07', 40)

WITH 
T1 AS
(
SELECT *, COALESCE(LEAD(A_DATE) OVER(ORDER BY A_DATE), '9999-12-31') AS END_DATE
FROM   A_TABLE
WHERE  A_VALUE IS NOT NULL
)
SELECT T.A_DATE, T1.A_VALUE
FROM   A_TABLE AS T
       JOIN T1 ON T.A_DATE >= T1.A_DATE AND T.A_DATE < T1.END_DATE;