Duplicating records to fill gap between dates

Question

I need to do something really weird, which is to create fake records in a view to fill the gap between posted dates of product prices. Actually, my scenario is a little bit more complicated than that, but I've simplified to products/dates/prices. Let's say we have this table: ``` create table PRICES_TEST ( PRICE_DATE date not null, PRODUCT varchar2(13) not null, PRICE number ); alter table PRICES_TEST add constraint PRICES_TEST_PK primary key (PRICE_DATE, PRODUCT); ``` With these records: ``` insert into PRICES_TEST values (date'2012-04-15', 'Screw Driver', 13); insert into PRICES_TEST values (date'2012-04-18', 'Screw Driver', 15); insert into PRICES_TEST values (date'2012-04-13', 'Hammer', 10); insert into PRICES_TEST values (date'2012-04-16', 'Hammer', 15); insert into PRICES_TEST values (date'2012-04-19', 'Hammer', 17); ``` selecting records will return me this: ``` PRICE_DATE PRODUCT PRICE ------------------------- ------------- ---------------------- 13-Apr-2012 00:00:00 Hammer 1…

Accepted Answer

I think I have a solution using an incremental approach toward the final result with CTE's:

```
with mindate as
(
  select min(price_date) as mindate from PRICES_TEST
)
,dates as
(
  select mindate.mindate + row_number() over (order by 1) - 1 as thedate from mindate,
    dual d connect by level <= floor(SYSDATE - mindate.mindate) + 1
)
,productdates as
(
  select p.product, d.thedate
  from (select distinct product from PRICES_TEST) p, dates d
)
,ranges as
(
  select
    pd.product,
    pd.thedate,
    (select max(PRICE_DATE) from PRICES_TEST p2
     where p2.product = pd.product and p2.PRICE_DATE <= pd.thedate) as mindate
    from productdates pd
)
select 
    r.thedate,
    r.product,
    p.price
from ranges r
inner join PRICES_TEST p on r.mindate = p.price_date and r.product = p.product
order by r.product, r.thedate
```

- `mindate` retrieves the earliest possible date in the data set

- `dates` generates a calendar of dates from earliest possible date to today.

- `productdates` cross joins all possible products with all possible dates

- `ranges` determines which price date applied at each date

- the final query links which price date applied to the actual price and filters out dates for which there are no relevant price dates via the `inner join` condition

Demo: http://www.sqlfiddle.com/#!4/e528f/126

Duplicating records to fill gap between dates

Solution

Problem