In enterprise database systems, one may commonly find business events (e.g. sales, revenue, etc) associated to dates or months. In such situations, it is somewhat straightforward to compute values (totals, averages, …) per date, per month or even per year. But it may be somewhat difficult to find the dates when the business event did not happen, as it may be necessary to create a list with every date in a time frame. In this article, we use a recursive query to create a list of dates and to find missing dates in a table (see also our post on using LEAD and LAG to find missing values).
Generating a list of dates
In order to do find the occurrence of a considered business event (e.g. sale), one just have to group the data on the date column and apply the adequate aggregate function in the value field.
For instance, let’s consider a Sales table. Such table stores the sales information, including the date and time when a sale occurred (in column Sales_Date) and the total value of the sales (in the column named Value). In the following figure, we present a SQL query that may be used to compute the total revenue per month in 2018 over a Sales table.
But in such context, it is not so easy to list the dates when the business event did not happen. For instance, as the Sales table only have tuples for the dates with sales, it may be necessary to create a list of all dates in a certain period in order to find the ones that do not appear in the Sales table.
To create a list of dates in Oracle, one may use a recursive query. In the Connect By clause, you should compute the difference between the initial and final dates and add 1. In Select, you should add a certain number of days (recursive level – 1) to the initial date. For instance, in the next query, we present how to generate the list of dates from January and February of 2018.
If we want to create a list of months, we can use the following query:
Finding missing dates
Now, we can create a query to list the list of dates with no sales in January and February of 2018. To do that, we use the date list query as a virtual relation in the following query.
In the first part of the above query, the virtual relation List contains all the dates in January and February of 2018. Then, the not exists clause is used to find the dates that does not appear in Sales table.