Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL Command for last full month activty and GROUP BY

Posted on 2011-02-15
2
Medium Priority
?
674 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
2 Comments
 
LVL 11

Accepted Solution

by:
jasonduan earned 2000 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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

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 …
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.
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

916 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