How to Generate a Complete Date Range in SQL, Including Missing Dates with Zero Values?

Generating a Complete Date Range with Missing Values

When working with date data, it's often necessary to display all dates within a specified range, even if records are missing for some dates. How do you achieve this in SQL, ensuring that missing dates show a zero value in relevant columns?

Consider the following table, @temp, with sample date data:

INSERT INTO @temp SELECT '10/2/2012', 1
INSERT INTO @temp SELECT '10/3/2012', 1
INSERT INTO @temp SELECT '10/5/2012', 1
INSERT INTO @temp SELECT '10/7/2012', 2
INSERT INTO @temp SELECT '10/9/2012', 2
INSERT INTO @temp SELECT '10/10/2012', 2
INSERT INTO @temp SELECT '10/13/2012', 2
INSERT INTO @temp SELECT '10/15/2012', 2

To retrieve all dates between two given dates, you can use the following query:

SELECT * FROM @temp WHERE CDate BETWEEN '10/01/2012' AND '10/15/2012'

However, this query will only return the existing records within that date range. To include missing dates with a zero value, you need to create a complete date range and join it with the existing data:

;WITH d(date) AS (
  SELECT CAST('10/01/2012' AS DATETIME)
  UNION ALL
  SELECT DATE + 1
  FROM d
  WHERE DATE < '10/15/2012'
  )
SELECT t.ID, d.date CDate, ISNULL(t.val, 0) AS val
FROM d
LEFT JOIN temp t
       ON t.CDate = d.date
ORDER BY d.date
OPTION (MAXRECURSION 0) -- Use this if your dates are >99 days apart

The MAXRECURSION number option limits the number of recursive iterations, ensuring the query completes successfully. The ISNULL function replaces any null values in the val column with zero.

Using this approach, you can ensure that all dates within the specified range are displayed, with missing values represented by zero.

The above is the detailed content of How to Generate a Complete Date Range in SQL, Including Missing Dates with Zero Values?. For more information, please follow other related articles on the PHP Chinese website!