Solved

SQL Command for last full month activty and GROUP BY

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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

A recent questions about how to add SSRS named instances, couldn't find any that talks about SQL server 2008, anyway I decided to help by creating some screen shots. The installation is straightforward, you just pop the SQL server 2008 installati…
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.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

757 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now