Solved

SQL Command for last full month activty and GROUP BY

Posted on 2011-02-15
2
669 Views
Last Modified: 2012-05-11
Hi all

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
WHERE        EXISTS
                             (SELECT        IncidentID, ModifiedOn
                               FROM            Incident
                               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.

Any ideas?

0
Comment
Question by:vistasupport
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 11

Accepted Solution

by:
jasonduan earned 500 total points
ID: 34896768
DECLARE @d1 DATETIME
DECLARE @d2 DATETIME

SET @d2 = CAST(LTRIM(STR(DATEPART(year, GETDATE()))) + '-' + LTRIM(STR(DATEPART(month,GETDATE()))) + '-01' AS DATETIME)
SET @d1 = DATEADD(month, -1, @d2)

SELECT        Category.CategoryName, COUNT(Category_Incident.IncidentID) AS TotalCallsPerCategory
FROM            Category_Incident INNER JOIN
                         Category ON Category_Incident.CategoryID = Category.CategoryID INNER JOIN
                         Incident  ON Incident.IncidentID = Category_Incident.IncidentID
WHERE        Incident.ModifiedOn >=@d1 AND  Incident.ModifiedOn < @d2
GROUP BY Category.CategoryName
ORDER BY TotalCallsPerCategory DES
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 34897834
you do not make it clear ...  you talk about incidents raised last month *ie January 2011

which would involve a notification date or incident raised date... not a modification date..

then you say you want to filter on a modification date...

how should the modification date be constrained? to only modifications on incidents
raised in Jan 2011 within Jan 2011 or since raising...

please restate your question...

what is it that you are trying to count?

also supply clairfy the position on gow you system raises incidents and deals with them...
(to audit the incidents and your help desk team you should be looking at the transaction/event of for the incidents... not some summary/globbed record)
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Written by Valentino Vranken. Introduction: The first step of creating a SQL Server Reporting Services (SSRS) report involves setting up a connection to the data source and programming a dataset to retrieve data from that data source.  The data…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question