Select Range of Dates, Including Ones With No Results

Question

Possible Duplicate:
SQL Server: How to select all days in a date range even if no data exists for some days

I wasn't really sure how to word this question, but I'll try to explain. I'm trying to build some basic reporting using queries like the following:

SELECT COUNT(*) AS count, h_date FROM (SELECT CONVERT(VARCHAR(10), h_time, 102) AS h_date FROM hits h GROUP BY h_date ORDER BY h_date

This returns results like this, which I use to build a graph:

8 2012.05.06
2 2012.05.07
9 2012.05.09

As you can see, it's missing the 8th as there were no hits on that day. Is there a way to get a value of 0 for dates that have no results, or will I have to parse the results after the fact and add them manually?

Hey Norla, thanks for the link. I guess I just hadn't worded it right when I searched. :) — ZachRabbit, Sep 18 '12 at 22:14

Aaron Bertrand · Accepted Answer · 2012-09-18T20:39:55.653

You can use existing catalog views to derive a sequential range of dates between your start date and your end date. Then you can just left join to your data, and any missing dates will be there with 0s.

DECLARE @min SMALLDATETIME, @max SMALLDATETIME;

SELECT @min = MIN(h_time), @max = MAX(h_time)
  FROM dbo.hits 
  -- WHERE ?

-- or if you just want a fixed range:

-- SELECT @min = '20120101', @max = '20120131';

;WITH n(d) AS 
(
  SELECT TOP (DATEDIFF(DAY, @min, @max)+1) 
   DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY [object_id]) - 1, DATEDIFF(DAY, 0, @min))
  FROM sys.all_objects ORDER BY [object_id]
)
SELECT n.d, [count] = COUNT(h.h_time)
  FROM n
  LEFT OUTER JOIN dbo.hits AS h
  ON h.h_time >= n.d
  AND h.h_time < DATEADD(DAY, 1, n.d)
  -- AND --WHERE clause against hits?
  GROUP BY n.d;

Wow, thanks! You even wrote in where column and table names would go, which is a big help to me. I hope to, one day, be an SQL wizard like you. :) — ZachRabbit, Sep 18 '12 at 22:18

score 1 · Answer 2 · answered Sep 19 '12 at 05:11

I've never been a big fan of using system tables to create dummy records to join against, but it's a very common approach.

I took Aaron Bertrand's answer and changed the Common Table Expression (CTE) to use a recursive one instead. It's quicker as it doesn't have to hit a table to do the query. Not that the previous version is slow anyway.

You need to specify "OPTION (MAXRECURSION 0);" otherwise it will limit the number of rows returned to the default (100). The value of 0 will return unlimited rows.

DECLARE @min SMALLDATETIME, @max SMALLDATETIME;

--SELECT @min = MIN(h_time), @max = MAX(h_time)
--  FROM dbo.hits 

SELECT @min = '20120101', @max = '20121231';


WITH recursedate(each_date, date_index) AS
(
SELECT @min, 0 

UNION ALL 
SELECT DATEADD(DAY,date_index+1,@min), date_index+1
    FROM recursedate
    WHERE DATEADD(DAY,date_index+1,@min) <= @max
)



SELECT recursedate.each_date, [count] = COUNT(h.h_time)
  FROM recursedate
  LEFT OUTER JOIN dbo.hits AS h
  ON --CONVERT(SMALLDATETIME,h.h_time) =  recursedate.dates 
  h.h_time >= recursedate.each_date
   AND h.h_time < DATEADD(DAY, 1, recursedate.each_date)
  -- AND --WHERE clause against hits?
  GROUP BY recursedate.each_date
   OPTION (MAXRECURSION 0); -- The default is 100 so you'll only get 100 dates, 0 is unlimited.

Select Range of Dates, Including Ones With No Results

2 Answers2

Linked