Filling in missing dates with values from the month before I have a table like this:

CREATE TABLE my_data (label text, value integer, date date);
INSERT INTO my_data (label, value, date) VALUES 
('AAA', 10, '2014-06-01'),
('AAA', 30, '2014-09-01'),
('AAA', 40, '2014-10-01'),
('AAA', 50, '2015-02-01'),
('BBB', 20, '2014-11-01'),
('BBB', 10, '2015-02-01'),
('BBB', 70, '2015-04-01');


and I need to fill up the missing dates (imagine a time series) in this way:

label | value | date
------+-------+------------
AAA   | 10    | 2014-06-01
AAA   | 10    | 2014-07-01
AAA   | 10    | 2014-08-01
AAA   | 30    | 2014-09-01
AAA   | 40    | 2014-10-01
AAA   | 40    | 2014-11-01
AAA   | 40    | 2014-12-01
AAA   | 40    | 2015-01-01
AAA   | 50    | 2015-02-01
AAA   | 50    | 2015-03-01
AAA   | 50    | 2015-04-01
BBB   | 20    | 2014-11-01
BBB   | 20    | 2014-12-01
BBB   | 20    | 2015-01-01
BBB   | 10    | 2015-02-01
BBB   | 10    | 2015-03-01
BBB   | 70    | 2015-04-01


where the time range goes from '2014-06-01' to '2015-04-01'.
What I did so far is:

WITH 
md AS
(
SELECT *, LEAD(date) OVER (PARTITION BY label ORDER BY date) AS next_date FROM my_data
),
calendar AS
(
select date::date from generate_series('2014-06-01'::date, '2015-04-01'::date, '1 month'::interval) date
)
SELECT m.label, m.value, c.date
FROM calendar c
JOIN md m
    ON c.date BETWEEN m.date AND (m.next_date - interval '1 month') order by label, date;
 label | value |    date    
-------+-------+------------
 AAA   |    10 | 2014-06-01
 AAA   |    10 | 2014-07-01
 AAA   |    10 | 2014-08-01
 AAA   |    30 | 2014-09-01
 AAA   |    40 | 2014-10-01
 AAA   |    40 | 2014-11-01
 AAA   |    40 | 2014-12-01
 AAA   |    40 | 2015-01-01
 BBB   |    20 | 2014-11-01
 BBB   |    20 | 2014-12-01
 BBB   |    20 | 2015-01-01
 BBB   |    10 | 2015-02-01
 BBB   |    10 | 2015-03-01
(13 rows)


The returned values of the query do not fill up the whole time range (until 2015-04). How can I make the query work in that way?

UPDATE

I think I have fixed it:

WITH 
md AS
(
SELECT *, LEAD(date) OVER (PARTITION BY label ORDER BY date) AS next_date FROM my_data
),
calendar AS
(
select date::date from generate_series('2014-06-01'::date, '2015-04-01'::date, '1 month'::interval) date
)
SELECT m.label, m.value, c.date
FROM calendar c
JOIN md m
    ON c.date BETWEEN m.date AND 
    (CASE WHEN m.next_date IS NULL THEN date '2015-04-01' ELSE m.next_date - interval '1 month' END) order by label, date;
First it generates a series of date by label and then you can use either a lateral join:




with a as
(
    select   label, generate_series(min(date), '2015-04-01'::date, interval '1 month') dt
    from     my_data
    group by label
)
select    label,
          t1.value,
           dt
from      a
left join lateral (select t1.value
                   from   my_data t1
                   where  t1.label = a.label
                   and    t1.date &lt;= a.dt
                   order by label, date desc
                   limit 1) t1 on true
order by  label, dt



or a subquery:




with a as
(
    select   label, generate_series(min(date), '2015-04-01'::date, interval '1 month') dt
    from     my_data
    group by label
)
select    label,
          (select t1.value
           from   my_data t1
           where  t1.label = a.label
           and    t1.date &lt;= a.dt
           order by label, date desc
           limit 1),
           dt
from      a
order by  label, dt



label | value | dt                    
:---- | ----: | :---------------------
AAA   |    10 | 2014-06-01 00:00:00+01
AAA   |    10 | 2014-07-01 00:00:00+01
AAA   |    10 | 2014-08-01 00:00:00+01
AAA   |    30 | 2014-09-01 00:00:00+01
AAA   |    40 | 2014-10-01 00:00:00+01
AAA   |    40 | 2014-11-01 00:00:00+00
AAA   |    40 | 2014-12-01 00:00:00+00
AAA   |    40 | 2015-01-01 00:00:00+00
AAA   |    50 | 2015-02-01 00:00:00+00
AAA   |    50 | 2015-03-01 00:00:00+00
AAA   |    50 | 2015-04-01 00:00:00+01
BBB   |    20 | 2014-11-01 00:00:00+00
BBB   |    20 | 2014-12-01 00:00:00+00
BBB   |    20 | 2015-01-01 00:00:00+00
BBB   |    10 | 2015-02-01 00:00:00+00
BBB   |    10 | 2015-03-01 00:00:00+00
BBB   |    70 | 2015-04-01 00:00:00+01



dbfiddle here