Solved

SQL Command for last full month activty and GROUP BY

Posted on 2011-02-15
2
668 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

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…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

738 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