Run query using each day of last month

Question

I have query that returns a count of records with a matching date:

DECLARE @StartDate AS DATE = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0)
DECLARE @EndDate AS DATE = DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())-1, -1)
SELECT
DISTINCT(CAST(Response_Date AS DATE)) AS Response_Date
,COUNT(CAST(Response_Date AS DATE)) AS Response_Count
...
WHERE Response_Date BETWEEN @StartDate AND @EndDate

And I get a return as expected.

^Response_Date^Response_Count^
|2016-09-22   |2             |
|2016-09-24   |4             |
...

However, these values will be used in report builder in lists and charts. The problem is that there are gaps between the dates. Is there some way to run the query returning all dates from previous months, and a 0 when nothing is on that date?

Here is my current query:

DECLARE @JurisdictionName AS CHAR(30) = 'Something'
DECLARE @StartDate AS DATE = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0)
DECLARE @EndDate AS DATE = DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())-1, -1)


SELECT
    DISTINCT(CAST(Response_Master_Incident.Response_Date AS DATE)) AS Response_Date
    ,COUNT(CAST(Response_Master_Incident.Response_Date AS DATE)) AS Response_Count
FROM 
    Response_Master_Incident 
WHERE EXISTS
    (SELECT * 
        FROM Activity_Log 
        WHERE 
        Activity_Log.Radio_Code IS NOT NULL 
        AND (Radio_Name LIKE 'V[0-9][0-9]' OR Radio_Name LIKE 'U[0-9][0-9]')
        AND Response_Master_Incident.ID = Activity_Log.Master_Incident_ID
        )
    AND Response_Master_Incident.Time_First_Unit_Assigned IS NOT NULL
    AND Response_Master_Incident.Response_Date BETWEEN @StartDate AND @EndDate
GROUP BY CAST(Response_Master_Incident.Response_Date AS DATE)

Answer 1

You have 2 good options that I see. One is to use a numbers table that you put into the database. This is simply a table that has a sequential list of numbers from 1 to 1,000,000 or any upper limit that you find useful. This table will allow you to accomplish what you want here, but you will also find many other useful things to do with it. See here for other potential uses of a number or tally table.

So first create a numbers table:

SELECT TOP 1000000 N=IDENTITY(INT, 1, 1)
INTO dbo.Numbers
FROM master.dbo.syscolumns a CROSS JOIN master.dbo.syscolumns  b;

ALTER TABLE dbo.Numbers ADD CONSTRAINT NBR_pk PRIMARY KEY(N);

From here we can write the query.

IF OBJECT_ID('tempdb..#test_dates') IS NOT NULL
  DROP TABLE #test_dates

CREATE TABLE #test_dates (Response_Date DATE, Response_Count INT)

INSERT INTO #test_dates (Response_Date, Response_Count)
VALUES ('8/3/2016',123),('8/12/2016',43)

DECLARE
  @JurisdictionName AS CHAR(30) = 'Something'
,  @StartDate AS DATE = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0)
,  @EndDate AS DATE = DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE()) - 1, -1);

WITH all_dates AS
       (SELECT DATEADD(day, n - 1, @StartDate) AS Dates
          FROM dbo.Numbers n
         WHERE n.n <= DATEDIFF(day, @StartDate, @EndDate) + 1)
    ,response_dates AS
       (  SELECT Response_Date
                , Response_Count
            FROM #test_dates
           WHERE Response_Date BETWEEN @StartDate AND @EndDate)
SELECT a.Dates, ISNULL(Response_Count, 0) AS Respose_Count
  FROM all_dates a LEFT JOIN response_dates r ON a.Dates = r.Response_Date

The other way to accomplish it is to use a date table. This table is very similar to the numbers table but it has all the dates for a time frame with some additional information about the dates. Again you will find many different uses for the table for the limited amount of space that it will take. See here for more information on how to create a date table and what you can use it for:

You could also use a recursive CTE to accomplish something similar, but it would likely have performance issues as things scale up.