Joining a calendar table on date to a table of items returns all NULLS (adding missing dates to tabl-CodePudding

I am trying to join a table of dates to an existing table of items. I want to add the missing dates for each item see items table:

original dataset

date    item    price
1/1/2018    toys    22.82
1/3/2018    toys    28.99
1/6/2018    toys    32.56
1/2/2018    shoes   41.23
1/4/2018    shoes   62.74
1/10/2018   shoes   35.16

I need to fill in missing dates for each item with a NULL price so then I can estimate it so I want this result: result wanted

date    item    price
1/1/2018    toys    22.82
1/2/2018    toys    NULL
1/3/2018    toys    28.99
1/4/2018    toys    NULL
1/5/2018    toys    NULL
1/6/2018    toys    32.56
1/2/2018    shoes   41.23
1/3/2018    shoes   NULL
1/4/2018    shoes   62.74
1/5/2018    shoes   NULL
1/6/2018    shoes   NULL
1/7/2018    shoes   NULL
1/8/2018    shoes   NULL
1/9/2018    shoes   NULL
1/10/2018   shoes   35.16

I have tried the following:

    select c.day, e.item, e.price
from db.days c cross join
     (select distinct item from db.items e)
     left join
     db.items e
     on c.day = e.date_added;

It gives me the following: result

date    item    price
1/1/2018    NULL    NULL
1/2/2018    NULL    NULL
1/3/2018    NULL    NULL
1/4/2018    NULL    NULL
1/5/2018    NULL    NULL
1/6/2018    NULL    NULL
1/2/2018    NULL    NULL
1/3/2018    NULL    NULL
1/4/2018    NULL    NULL
1/5/2018    NULL    NULL
1/6/2018    NULL    NULL
1/7/2018    NULL    NULL
1/8/2018    NULL    NULL
1/9/2018    NULL    NULL
1/10/2018   NULL    NULL

How can I get rid of the NULLs and get my desired result above? I am using SQLite Studio

CodePudding user response：

You can use a recursive cte:

with recursive cte(d) as (
   select min(date) from toys
   union all
   select date(c.d, ' 1 day') from cte c where c.d < (select max(date) from toys)
)
select c.d, t1.item, t4.price from cte c 
cross join (select distinct t.item from toys t) t1 
left join toys t4 on t4.date = c.d and t4.item = t1.item 
where c.d <= (select max(t3.date) from toys t3 where t3.item = t1.item) and c.d >= (select min(t3.date) from toys t3 where t3.item = t1.item)

CodePudding user response：

Try this, I think it will work =)

   select c.day, e.item, 
   (select price
    from items i
    where i.date_added = c.day and i.item = e.item) price
   from days c left join (select distinct item, min(date_added) min_date, max(date_added) max_date from items group by item) e
   where (c.day BETWEEN  min_date and max_date)
   order by item, day