-
Re: Generate a report based on each month (not just the previous month)
zzz May 14, 2013 5:24 PM (in response to slidermike)This is how the built-in reports do Group by Month.
SELECT
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).
-
Re: Generate a report based on each month (not just the previous month)
slidermike May 16, 2013 11:12 AM (in response to zzz)Thanks zzz,
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.
-
-
Re: Generate a report based on each month (not just the previous month)
njoylifMay 14, 2013 7:04 PM (in response to slidermike)
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.
-
Re: Generate a report based on each month (not just the previous month)
slidermike May 16, 2013 11:17 AM (in response to njoylif)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.
-