Solved

SQL Command for last full month activty and GROUP BY

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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

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…
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

617 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