I have a small helpdesk system based on SQL Server 2008. I'm using SQL Reporting Services to produce regular reports from the system. What I'm looking to do is to extract all the incidents logged last month and group them by the incident type.
Note: when I say last month, I mean the last full month not the previous 30 days. So for example, this month is Feb - I want all incidents logged during January.
I have 3 tables:
incident - lists all the incidents. Relevant fields are IncidentID and ModifiedOn
category_incident - links incidents to category. Contains two field - IncidentID and CategoryID
category - lists the category types. Two fields - CategoryID and CategoryName
So the incident table will have for example:
Incident ID = 10, Modified on = '2011-01-05 10.45.12'
Category_Incident will have:
Incident ID = 10, Category ID = 4
Category will have:
Category ID = 4, Category Name = 'Microsoft Office'
I can group them OK by using the following command:
SELECT Category.CategoryName, COUNT(Category_Incident.IncidentID) AS TotalCallsPerCategory
FROM Category_Incident INNER JOIN
Category ON Category_Incident.CategoryID = Category.CategoryID
(SELECT IncidentID, ModifiedOn
WHERE (IncidentID = Category_Incident.IncidentID))
GROUP BY Category.CategoryName
ORDER BY TotalCallsPerCategory DESC
This gives me all the calls on the database, I'm unable to find a command where I can filter the ModifiedOn field to only include the last full month's incidents.