mhsheldesk
asked on
Select total count for each day of the month
Hi,
I'd like to query our database for the total number of events per day. I have attached what I have written so far. I'm most interested for when the total is 0, and this query doesn't present 0 in its rowset.
How can I get this to present 0 and the date when the total is 0 for a particular day of the month
Thanks
I'd like to query our database for the total number of events per day. I have attached what I have written so far. I'm most interested for when the total is 0, and this query doesn't present 0 in its rowset.
How can I get this to present 0 and the date when the total is 0 for a particular day of the month
Thanks
SELECT DATEPART(MONTH, created) AS 'Month',
DATEPART(day, created) AS N'Day', COUNT(*)
FROM dbo.table
GROUP BY DATEPART(MONTH, Created), DATEPART(day, Created)
ORDER BY DATEPART(MONTH, Created), DATEPART(day, Created)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You have to create some calendar table for given period and (outer) join it with your results.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You probably noted the bug in join condition:
ON tt.iYear = c.Year AND tt.iMonth ...
is correctly
ON tt.iYear = c.iYear AND tt.iMonth ...
ON tt.iYear = c.Year AND tt.iMonth ...
is correctly
ON tt.iYear = c.iYear AND tt.iMonth ...
ASKER
Thank you very much for your solutions, I spilt this since lwadwell answered first and pcelba gave a complete solution. Thank you again.