This is how the built-in reports do Group by Month.
CONVERT(DateTime, LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)), 101) AS SummaryMonth,
GROUP BY CONVERT(DateTime, LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)), 101),
ORDER BY SummaryMonth ASC,
After that, you still need to extend the start date to what month you want to see. ie:
DATEADD(m, DATEDIFF(m,0,DATEADD(m,-2,GETDATE())), 0) = 1st day of 2 month ago (3/1/2013).
Its nice to look at other reports & see how they are doing what they do.
I may look into creating one based upon this query.
i would suggest you schedule the report to run on the first of every month and have it automatically email your boss and copy yourself to ensure it is going out properly.
Report scheduler can be accessed by RDPing into the primary NPM box.
You are spot on njoylif,
That's exactly what I did.
Created the report; had it run & email automatically to the boss on the 1st of each month.
The issue we ran into was the boss did NOT collect the data each month & was storing the links thinking he could go back later (in this case 2 months) and choose the hyperlink to get the data at a much later date.
It turns out there is only 1 minor adjustment needed to the existing query to go back however many months (though it wont pull it for a given month only but collectively back from when you run it to x previous months.
The issue I realized was that my DB was archiving only 30 days of the events data so I cant see anything past 30 anyway no matter what I put into the query string. Cannot pull data that isn't there.
Anyway the trick in the existing query is to change the "-1" to "-3" for the previous 3 months (after having changed the DB archiving).
eventtime between (DATEADD(m, DATEDIFF(m, 0, getdate()) -1 , 0)) AND (DATEADD(m, DATEDIFF(m, 0, getdate()) , 0))
eventtime between (DATEADD(m, DATEDIFF(m, 0, getdate()) -3 , 0)) AND (DATEADD(m, DATEDIFF(m, 0, getdate()) , 0))
Thank you guys for the help.