I am trying to add missing dates to my query so that my results look like this:
10/22/2018 15
10/21/2018 0
10/20/2018 14
Rather than this:
10/22/2018 15
10/20/2018 14
I want the past 300 days listed, even if the output value is 0.
Here is my query:
SELECT TOP (300)
CAST(createddate as DATE),
count(DISTINCT ID)
FROM table
GROUP BY CAST(createddate as DATE)
ORDER BY CAST(createddate as DATE) DESC
You can use a recursive CTE to generate the data:
WITH dates as (
SELECT MAX(CAST(createddate as date)) as dte, 1 as lev
FROM table
UNION ALL
SELECT DATEADD(day, -1, dte), lev + 1
FROM dates
WHERE lev < 300
)
SELECT COUNT(DISTINCT t.ID)
FROM dates d LEFT JOIN
table t
ON d.dte = CAST(t.createddate as DATE)
GROUP BY d.dte
ORDER BY d.dte DESC
OPTION (MAXRECURSION 0);
