When counting the number of records per day, how do I include the days that have zero records?

Question

I have a query that tell me how many records were created each day, but if there are no records created on a day then the query does not list the date and zero amount.

Example

How do I include those dates?

SELECT Convert(VARCHAR, CreatedDate, 101) as "Date", count(result_ID) as "Results"
FROM Results
WHERE CreatedDate >=dateadd(day,datediff(day,0,GetDate())- 90,0)
AND Verified = 1
GROUP BY Convert(VARCHAR, CreatedDate, 101)
Order by "Date" desc

You have to maintain one calender table which could hold all possible dates. — Yogesh Sharma, May 01 '18 at 18:50
It depends what you mean by that. If your data does not include those dates (which it looks like it may not), then what Yogesh said is true. Otherwise, you would just need to relax your query (possibly the "Verified = 1" restriction pushes those dates out). Without knowing more about the data itself, this is really outside of the range of a reasonable answer. — ccarpenter32, May 01 '18 at 18:55
Which [DBMS product](https://en.wikipedia.org/wiki/DBMS) are you using? "SQL" is just a query language, not the name of a specific database product. Please add a tag for the database product you are using `postgresql`, `oracle`, `sql-server`, `db2`, ... — , May 02 '18 at 13:27

sniperd · Answer 1 · 2018-05-02T13:04:07.833

1

Well, I have answer but you are not going to like it. What I had to end up doing is pre-populate a calendar table for everyday for the next decade(or 100 years or whatever) and LEFT OUTER JOIN from it to your data table. That way you have a row for everyday, and you can just ISNULL(0) your Results in your query.

This was a solution we had to do at my former company, seems hacked up but it does work and is easy troubleshoot. Anything else we tried making dynamic data sets, very clever SQL, UNION multiple data sets, etc. just ended up having trouble later and was just felt over complicated. This solution is pretty simple.

Here is code to do the calendar table: How to create a Calendar table for 100 years in Sql

edited May 02 '18 at 13:04

answered May 01 '18 at 19:02

sniperd

5,124
6
28
44

1

Calendar tables are quite common and useful - I wouldn't consider it a hack. – Andrew May 01 '18 at 19:05
Thanks @Andrew glad to hear we didn't go down the wrong path :) My team just felt that there should have been some command in SQL or way to do it. Once we had the table we used it a LOT! – sniperd May 01 '18 at 19:07
1

There is, Yogesh posted it before I finished my answer, so see that :) – Andrew May 01 '18 at 19:08

score 0 · Answer 2 · answered May 01 '18 at 19:08

If, you don't have calender table then you can use recursive query

with cte as (
    select MIN(date) mndt, MAX(date) mxdt
    from Results
    union all
    select DATEADD(DAY, 1, mndt) mndt, mxdt 
    from cte c
    where mndt < mxdt
)
select c.mndt as date,
       (select count(result_ID) from Results where date = c.mndt) as Results
from cte c;

When counting the number of records per day, how do I include the days that have zero records?

2 Answers2