SQL (Postgres) fill in missing dates with values from last non empty day

Question

I have a table with dates, product code and price. But I have dates for business days, so it is missing weekends and holidays. I would like to add any missing days, so that those days contain the previous non-empty price.

For example: product_info

Date         prod_code  price
2019-12-12   123        17.00
2019-12-12   456        20.00
2019-12-12   789        22.00
2019-12-13   123        18.50
2019-12-13   456        21.50
2019-12-13   789        22.50
2019-12-16   123        17.00
2019-12-16   456        20.00
2019-12-16   789        22.00

I would like to be able to update/insert records such that the table would contain:

Date         prod_code  price
2019-12-12   123        17.00
2019-12-12   456        20.00
2019-12-12   789        22.00
2019-12-13   123        18.50
2019-12-13   456        21.50
2019-12-13   789        22.50
2019-12-14   123        18.50
2019-12-14   456        21.50
2019-12-14   789        22.50
2019-12-15   123        18.50
2019-12-15   456        21.50
2019-12-15   789        22.50
2019-12-16   123        17.00
2019-12-16   456        20.00
2019-12-16   789        22.00

From what I've searched for online, I probably will need to use a calendar table and perhaps some window functions, but Im not sure how to get started. Would greatly appreciate some help here.

Gordon Linoff · Accepted Answer · 2020-01-02 12:11:43Z

1

Use can use generate_series() to capture the dates. Then you can use a lateral join to fill in the data:

select gs.date, p.prod_code, t.price
from generate_series('2019-12-12'::date, '2019-12-19'::date, interval '1 day') gs(dte) cross join
     (select distinct prod_code from t) p left join lateral
     (select t.price
      from t
      where t.date <= gs.date and
            t.prod_code = p.prod_code
      order by t.date desc
      limit 1
     ) t
     on 1=1;

edited Jan 2, 2020 at 12:11

answered Jan 1, 2020 at 15:59

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

4 Comments

tman Over a year ago

in your example above, are p and t separate tables, or is p an alias back to the t table, and thus only one actual source table plus the generate_series?

Gordon Linoff Over a year ago

p is a table alias.

tman Over a year ago

hmm, ok, thats what I thought. When I run the query Im getting back gs.prod_code does not exist.

tman Over a year ago

Ah, I think it works with t.prod_code = p.prod_code, instead of t.prod_code = gs.prod_code.

Collectives™ on Stack Overflow

SQL (Postgres) fill in missing dates with values from last non empty day

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related