force number of rows to return in date range from SQL query

Question

I'm running a query on our SQL (2012) database which returns a count of records in a given date range, grouped by the date.

For example:

Date    Count
12/08   12
14/08   19
19/08   11

I need to fill in the blanks as the charts I plot get screwed up because there are missing values. Is there a way to force the SQL to report back a blank row, or a "0" value when it doesn't come across a result?

My query is

SELECT TheDate, count(recordID) 
FROM myTable 
WHERE (TheDate between '12-AUG-2013 00:00:00' and '20-AUG-2013 23:59:59')
GROUP BY TheDate

Would I need to create a temp table with the records in, then select from that and right join any records from myTable?

Thanks for any help!

score 2 · Accepted Answer · answered Oct 16 '13 at 14:38

If you create a (temporary or permanent) table of the date range, you can then left join to your results to create a result set including blanks

 SELECT dates.TheDate, count(recordID) 
 FROM 
 ( select 
      convert(date,dateadd(d,number,'2013-08-12')) as theDate 
   from master..spt_values 
   where type='p' and number < 9
 ) dates
 left join yourtable on dates.thedate = convert(date,yourtable.thedate)
 GROUP BY dates.TheDate

score 0 · Answer 2 · edited May 23 '17 at 12:10

A temp table would do the job but for such a small date range you could go even simpler and use a UNION-ed subquery. E.g:

SELECT dates.TheDate, ISNULL(counts.Records, 0)
FROM
(SELECT TheDate, count(recordID) AS Records
 FROM myTable 
 WHERE (TheDate between '12-AUG-2013 00:00:00' and '20-AUG-2013 23:59:59')
 GROUP BY TheDate
) counts
RIGHT JOIN
(SELECT CAST('12-AUG-2013' AS DATETIME) AS TheDate
 UNION ALL SELECT CAST('13-AUG-2013' AS DATETIME) AS TheDate
 UNION ALL SELECT CAST('14-AUG-2013' AS DATETIME) AS TheDate
 UNION ALL SELECT CAST('15-AUG-2013' AS DATETIME) AS TheDate
 UNION ALL SELECT CAST('16-AUG-2013' AS DATETIME) AS TheDate
 UNION ALL SELECT CAST('17-AUG-2013' AS DATETIME) AS TheDate
 UNION ALL SELECT CAST('18-AUG-2013' AS DATETIME) AS TheDate
 UNION ALL SELECT CAST('19-AUG-2013' AS DATETIME) AS TheDate
 UNION ALL SELECT CAST('20-AUG-2013' AS DATETIME) AS TheDate
) dates
ON counts.TheDate = dates.TheDate

Here's a SQL Fiddle Demo.

If you need a more generic (but also more complex) solution, take a look at this excellent answer (by @RedFilter) to a similar question.

force number of rows to return in date range from SQL query

2 Answers2