AdF AdF - 27 days ago 8
SQL Question

sql completion of a result where it miss some results in the sequence

I have a table with sales information

like this:

|product | sales | date|


Most of the time the date are consecutive from 201601 to 201652.
but some times there is a gap ex : no line for 201602 for productA

How can I make an SQL query that will return a result for this gap like this :

productA,4,201601

**productA,0,201602**

productA,5,201603

productA,8,201604

(...)


instead of :

productA,4,201601

productA,5,201603

productA,8,201604

(...)


Of course it will also be some product B,C,...

Answer

You do this by using cross join to get all the rows and then left join to pull in the values.

Assuming you have some data for each week:

select p.product, d.date, coalesce(s.sales, 0) as sales
from (select distinct product from sales) p cross join
     (select distinct date from sales) d left join
     sales s
     on s.product = p.product and s.date = d.date;

If you have tables of products and dates, you can use those instead of the subqueries.