SQL Find Missing Dates in a Specified Date Range

Identifying missing dates within a specified range is a crucial aspect in various data analysis scenarios, especially when dealing with time-series data such as sales records, financial transactions, or any temporal events. The significance lies in uncovering gaps or lapses in the dataset, as missing dates could indicate potential discrepancies, data entry errors, or periods without recorded activity. This process is invaluable for maintaining data integrity, ensuring completeness in historical records, and facilitating accurate trend analysis. By finding and addressing missing dates, analysts and data professionals can enhance the reliability of their datasets, leading to more robust insights and informed decision-making based on a comprehensive understanding of the temporal aspects of the data.

In this post, we will demonstrate how to use Recursive CTE to construct a virtual Calendar table and then use LEFT JOIN technique to find missing dates within a specified date range.

tip

You can use this SQL Server Fiddle to follow along and practice finding missing dates in a range

SQL Server DB Fiddle: Find Missing Dates in Range

Daily Sales Data

We create a table to store daily sales data, inserts sample rows with specific sales information, and then verifies the inserted data by retrieving all rows from the table.

DailySales Table

-- Create DailySales table
CREATE TABLE DailySales (
    id INT PRIMARY KEY,
    sale_date DATE,
    sales_amount DECIMAL(10, 2)
);

-- Insert sample rows
INSERT INTO DailySales (id, sale_date, sales_amount) VALUES
(1, '2024-01-01', 1000.50),
(2, '2024-01-02', 1500.75),
(3, '2024-01-04', 1200.25),
(4, '2024-01-06', 800.00),
(5, '2024-01-07', 2000.50);

-- Verify the inserted rows
SELECT * FROM DailySales;

id	sale_date	sales_amount
1	2024-01-01	1000.50
2	2024-01-02	1500.75
3	2024-01-04	1200.25
4	2024-01-06	800.00
5	2024-01-07	2000.50

Find Missing Dates in a Specified Date Range

We design the following script to find missing sale dates within a specified date range.

Find Missing Dates in a Specified Sales Date Range

DECLARE @StartDate DATE = '2024-01-01';
DECLARE @EndDate DATE = '2024-01-07';

WITH Calendar AS (
    SELECT @StartDate AS date_value
    UNION ALL
    SELECT DATEADD(DAY, 1, date_value)
    FROM Calendar
    WHERE date_value < @EndDate
)

SELECT C.date_value AS missing_sale_date
FROM Calendar C
LEFT JOIN DailySales DS ON C.date_value = DS.sale_date
WHERE DS.sale_date IS NULL;

The SQL query detects that between 2024-01-01 and 2024-01-07 there are 2 days with no sales.

missing_sale_date
2024-01-03
2024-01-05

Here's a breakdown of each part of the script:

1. DECLARE @StartDate and @EndDate:

   • Two variables, @StartDate and @EndDate, are declared with the data type DATE.
   • @StartDate is assigned the value '2024-01-01', and @EndDate is assigned the value '2024-01-07'.
   • These variables define the date range for which we want to find missing sale dates.

2. WITH Calendar AS (...):

   • This section uses a common table expression (CTE) named Calendar to generate a series of dates within the specified range.
   • The CTE starts with a single row containing @StartDate.
   • The UNION ALL operator is used to recursively add rows to the result set, with each row representing the next consecutive date in the sequence.
   • The recursion continues until the date value in the CTE (date_value) is less than @EndDate.

3. SELECT C.date_value AS missing_sale_date:

   • This is the final query that uses the generated calendar of dates and the DailySales table to find missing sale dates.
   • It performs a LEFT JOIN between the Calendar CTE and the DailySales table on the condition where the date values match (C.date_value = DS.sale_date).
   • The WHERE clause filters the results to include only those where there is no corresponding entry in the DailySales table (DS.sale_date IS NULL).
   • As a result, this query returns the dates within the specified range that are missing from the DailySales table, indicating potential days with no sales.

Conclusion

In summary, this script creates a date range using a CTE named Calendar and then identifies the dates within that range that do not have corresponding entries in the DailySales table, representing days with no sales.