How do I bring back an entire range of dates in SQL between two dates, even when there is no data?

Question

Trying to get the number of incidents in my database, in the format:

date   numberOfIncidents
date   numberOfIncidents
date   numberOfIncidents
date   numberOfIncidents

I supply the start date / end date to my SQL stored procedure, but if there is no entry in the database for a particular date (because no incidents occured) then I wont get the full range back.

What I want is every single date to be returned between my start/end dates, with a value next to it.

Is this possible in SQL Server?

Answer 1

You can use a recursive common table expression to produce the date range and then join it to your incidents.

WITH DateRange(date) AS
(
  SELECT @dateFrom date
  UNION ALL
  SELECT DATEADD(dd, 1, date) date FROM DateRange WHERE date < @dateTo
)

SELECT DateRange.date, count(incident.id) 
FROM DateRange
LEFT OUTER JOIN incident
  ON incident.date >= DateRange.date 
  AND incident.date < DATEADD(dd, 1, DateRange.date)
GROUP BY DateRange.date
ORDER BY DateRange.date

By default, SQL Server limits the recursive definition to 100 rows. If you need more than 100 date rows, append OPTION (MAXRECURSION n) to the end of the query, changing n to the required number of rows.