From the sales figures above, we can see the missing dates are:

1997-06-01
1997-06-07
1997-06-08
1997-06-14
1997-06-15
1997-06-21
1997-06-22
1997-06-28
1997-06-29

Our approach is to use just SQL select queries, no variables and/or functions are needed. The missing dates would be filled with 0.00 in the Sales column.

First, you need to create a calendar table (date dimension) in your database. In our Northwind database, we created a calendar table called DATE_DIM which contains every date and its relevant information from 01/Jan/1960 to 31/12/2122. You can download the SQL script to create DATE_DIM.

Here is a snippet of the data in DATE_DIM.

Because the DATE_DIM contains every date in history, we can use it to patch the missing dates where no sales are generated. To do the patching, we can use either LEFT JOIN or UNION.

1. Patch the gaps by using LEFT JOIN

Query below uses two derived tables and then left join the first derived table with the second derived table. The first derived table selects all dates from DATE_DIM for June 1997. The second derived table extracts aggregated sales figures for each date in June 1997. Note that the second derived table contains gaps and the gaps are filled by the first derived table.

select t1.date_iso as OrderDate, 
    case when t2.sales is null then 0 
        else t2.sales 
    end as Sales
from 
(
    select DATE_ISO
    from date_dim
    where year=1997 and month_num=6
) as t1 -- this is the 1st derived table.
left join
(
    select date(t2.OrderDate) as OrderDate,
        round(sum(t1.Quantity*t1.UnitPrice*(1-t1.Discount)),2) as Sales 
    from order_details as t1
    join orders as t2 on t1.OrderID=t2.OrderID
    where year(t2.OrderDate)=1997 and month(t2.OrderDate)=6
    group by t2.OrderDate
) as t2 -- this is the 2nd derived table.
on t1.date_iso=t2.OrderDate;

Query result set - 30 rows returned

2. Patch the gaps by using UNION

Similar to the query above, the following query uses UNION to vertically append two result sets from two SELECT queries. The result set then forms a derived table and further SUM is applied to get the final result.

/*
This query returns identical result set as the left 
join query above.
*/
select OrderDate, round(sum(Sales),2) as Sales
from
(
    select DATE_ISO as OrderDate, 0 as Sales
    from date_dim
    where year=1997 and month_num=6
    union 
    select date(t2.OrderDate) as OrderDate,
        round(sum(t1.Quantity*t1.UnitPrice*(1-t1.Discount)),2) as Sales 
    from order_details as t1
    join orders as t2 on t1.OrderID=t2.OrderID
    where year(t2.OrderDate)=1997 and month(t2.OrderDate)=6
    group by t2.OrderDate
) as t1
group by OrderDate;

Happy Coding!

Other tutorials in this category

1. MySQL Northwind Queries - Part 1

2. MySQL Northwind Queries - Part 2

3. MySQL Northwind Queries - Part 3

4. How to Work with Two Unrelated Values

5. How to Calculate Totals, Subtotals and Grand Total

6. How to Work with NULL Values

7. How to fill down empty cells with values from a previous non-empty row

8. Use RANK function to update a previous record within a group or partition

9. Two ways to add a unique number or ID to each row

10. 3 ways to get Top N rows from MySQL

11. How to generate Cumulative Sum (running total) by MySQL - Part 1

12. How to generate Cumulative Sum (running total) by MySQL - Part 2

Back to Tutorial Index Page