Here is my original query:
SELECT
CAST(IndexedDate as varchar),
COUNT(*) AS Logins
FROM
Table
WHERE
EventType = 'Login'
AND IndexedDate > DATEADD(mm, -1, GETDATE())
GROUP BY
IndexedDate
ORDER BY
IndexedDate DESC
This would leave gaps, for example:
2016-09-13 41 2016-09-12 31 2016-09-09 15 2016-09-08 36
Based on this question, I tried the following and still received the gaps but on top of that the results were wrong (the numbers were MUCH higher):
SELECT
CAST(IndexedDate as varchar),
SUM(Case When COUNT(*) Is Null Then 0 Else COUNT(*) End) AS Logins
FROM
...
How can I get my results to look like this?
2016-09-13 41 2016-09-12 31 2016-09-11 0 2016-09-10 0 2016-09-09 15 2016-09-08 36
I've checked a few other questions but they all involve joins or other factors not in my scenario.
UPDATE
Based on comments, I've attempted an OUTER JOIN. This iteration finally ran successfully, but the results were a bit backwards...
SELECT
CAST(a.IndexedDate as varchar) as dt,
COUNT(*) AS Logins
FROM
(
SELECT *
FROM Table
WHERE IndexedDate > DATEADD(mm, -1, GETDATE())
AND EventType = 'Login'
) a
FULL OUTER JOIN (
SELECT DISTINCT(IndexedDate)
FROM Table
WHERE IndexedDate > DATEADD(mm, -1, GETDATE())
) b
ON
a.IndexedDate = b.IndexedDate
GROUP BY
b.IndexedDate
ORDER BY
b.IndexedDate DESC
Results:
2016-09-13 41 2016-09-12 31 (null) 1 (null) 1 2016-09-09 15 2016-09-08 36
I verified that aggregate b includes the missing dates.
So I flipped the aggregates from the edit to my original post and now it's working:
Query
SELECT
CAST(a.IndexedDate as varchar) as dt,
COUNT(EventType) AS Logins
FROM
(
SELECT DISTINCT(IndexedDate)
FROM Table
WHERE IndexedDate > DATEADD(mm, -1, GETDATE())
) a
FULL OUTER JOIN (
SELECT *
FROM Table
WHERE IndexedDate > DATEADD(mm, -1, GETDATE())
AND EventType = 'Login'
) b
ON
a.IndexedDate = b.IndexedDate
GROUP BY
a.IndexedDate
ORDER BY
a.IndexedDate DESC
Results
2016-09-13 41 2016-09-12 31 2016-09-11 0 2016-09-10 0 2016-09-09 15 2016-09-08 36
Note that I had to replace COUNT(*) with COUNT(EventType) so it wouldn't count the date from the aggregate which was resulting in a 1.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With