Given a table Event
containing a field called EventTime
of type DateTime and that the value will contain both date and time elements, I need create a summary query which counts the number of events in each month.
The resulting type of the Group By field must also be Date Time with a 0 time element and set to 1st day of the month.
This is what I have so far but its not very elegant and I'm not sure its particularly efficient.
SELECT COUNT(1) AS [CountOfEvents],
DATEADD(d, 1 - DAY(EventTime), DATEADD(dd, 0, DATEDIFF(dd, 0, EventTime))) AS [Month]
FROM [Event]
GROUP BY DATEADD(d, 1 - DAY(EventTime), DATEADD(dd, 0, DATEDIFF(dd, 0, EventTime)))
Better suggestions for either more efficiency or elegance?