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

It's a bit hard to tell without more information, but it sounds like what you need to do is create a temp table that has your complete date range in it, then use a left join to join it to your numberOfIncidents data. Then you'll get all the dates, even if there were no incidents.

EDIT: To create the temp table, you could just create a loop that goes from your startDate to your endDate, and insert those rows into the db.

Try this code:

-- These dates are just for example purposes
declare @startDate datetime
declare @endDate datetime
set @startDate = '2009-11-20'
set @endDate = '2009-12-17'


while @startDate < @endDate
    begin
        -- This line is where you insert into your temp table.  I'm just
        -- printing the value out, so you can se what's happening.
        print @startDate
        -- Go to the next day.
        set @startdate = dateadd(dd, 1, @startdate)
    end

Answer 2

Yes - you can use a Numbers Table a bit like this (probably not overly efficient, and assumes incident dates have zero time parts (00:00:00) - if this is not true you'll need to make the time zero test the difference in dates):

CREATE PROC dbo.GetRange @startdate datetime, @enddate datetime
AS BEGIN
     -- Setting up a numbers table...
    WITH Nbrs_3( n ) AS ( SELECT 1 UNION SELECT 0 ),
    Nbrs_2( n ) AS ( SELECT 1 FROM Nbrs_3 n1 CROSS JOIN Nbrs_3 n2 ),
    Nbrs_1( n ) AS ( SELECT 1 FROM Nbrs_2 n1 CROSS JOIN Nbrs_2 n2 ),
    Nbrs_0( n ) AS ( SELECT 1 FROM Nbrs_1 n1 CROSS JOIN Nbrs_1 n2 ),
    Nbrs ( n ) AS ( SELECT 1 FROM Nbrs_0 n1 CROSS JOIN Nbrs_0 n2 ),
    N AS (SELECT n = ROW_NUMBER() OVER (ORDER BY n) FROM Nbrs ),
     -- Getting the full date range
    DateRange AS ( SELECT thedate = DATEADD(day,N.n-1,@startdate)
      FROM N
      WHERE N.n <= DATEDIFF(day,@startdate,@enddate)+1  )
    SELECT D.thedate
         , numincidents = (SELECT count(*) 
                    FROM TBL_INCIDENTS I 
                    WHERE I.incdate = D.thedate)
    FROM DateRange D 
    GROUP BY D.thedate

END
GO

Then use as..

exec GetRange @startdate = {d '2009-02-01'},  @enddate = {d '2009-03-31'}

Answer 3

How about something like this (admittedly this isn't a stored procedure, but it should be useful):

DECLARE @start_date datetime,
        @end_date   datetime

SELECT @start_date = '2009-12-01', @end_date = '2009-12-31'

DECLARE @d datetime
SET @d = @start_date
WHILE @d < dateadd(d, 1, @end_date)
BEGIN
  PRINT @d
  SET @d = dateadd(d, 1, @d)
END

Answer 4

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.