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?
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.
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'}
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
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
