|
Custom Search
| |
|
How to Fill Gaps in Sales Data Suppose you need to create a report showing total sales for each date in a month. Your query would use SUM and GROUP BY, but what if there are order days you don't have any sales activity? In this case, your query result would have gaps because you don't have sales data for those days. Here in this tutorial, we'll look at how to fill those gaps even though no sales data are available in our Order table.
From the sales figures above, we can see the missing dates are:
1997-06-01 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, 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. /*
Happy Coding! Other tutorials in this category 1. MySQL Northwind Queries - Part 1 |
Copyright © 2024 GeeksEngine.com. All Rights Reserved. This website is hosted by HostGator. No portion may be reproduced without my written permission. Software and hardware names mentioned on this site are registered trademarks of their respective companies. Should any right be infringed, it is totally unintentional. Drop me an email and I will promptly and gladly rectify it. |
Home | Feedback | Terms of Use | Privacy Policy |