Do you use System Center Service Manager, and do you or your boss want to know how many Incident Requests the support team are resolving within the same day as it was created? If yes, then you are on the right blog post.
Run the query below against the Data Warehouse database DWDataMart, and you will get a monthly trend report on resolved Incident Requests.
Configure variables in the Query
There are two variable in the Query you need to configure before you run it.
SET @Year = 2014; define the year the request was resolved
SET @Supportgroup = ‘1. Level Support’; define the support group you want analyze
/*
*******************************************************************************
Author: Brian Fahrenholtz, Coretech A/S. https://blog.ctglobalservices.com
Purpose: This SQL Query return the numbers of Incidents that was
resolved within the same day as it was created.
Usage: Run the query on the DWDataMart database.
History:
1.0.0 BFA 12/04/2014 Created initial version.
*******************************************************************************
*/
-- Variable declarations
DECLARE @Year VARCHAR(50);
DECLARE @Supportgroup VARCHAR(50);
-- Initialize the variable.
SET @Year = 2014;
SET @Supportgroup = '1. Level Support';
-- Main routines
SELECT a.ResolvedMonth AS 'Month', a.IncidentResolved AS 'Total IR Resolved', b.IncidentResolved AS 'Resolved within the same day' FROM
(SELECT IncidentResolved.ranges AS 'ResolvedMonth', count(*) AS 'IncidentResolved' FROM
(
SELECT CASE
WHEN DATEPART(month,I.ResolvedDate) = '1' then '1'
WHEN DATEPART(month,I.ResolvedDate) = '2' then '2'
WHEN DATEPART(month,I.ResolvedDate) = '3' then '3'
WHEN DATEPART(month,I.ResolvedDate) = '4' then '4'
WHEN DATEPART(month,I.ResolvedDate) = '5' then '5'
WHEN DATEPART(month,I.ResolvedDate) = '6' then '6'
WHEN DATEPART(month,I.ResolvedDate) = '7' then '7'
WHEN DATEPART(month,I.ResolvedDate) = '8' then '8'
WHEN DATEPART(month,I.ResolvedDate) = '9' then '9'
WHEN DATEPART(month,I.ResolvedDate) = '10' then '10'
WHEN DATEPART(month,I.ResolvedDate) = '11' then '11'
WHEN DATEPART(month,I.ResolvedDate) = '12' then '12'
ELSE 'x'
END AS Ranges
FROM
DWDataMart.dbo.IncidentDimvw I
INNER JOIN DWDAtaMart.dbo.EntityDimvw Entity
ON I.EntityDimKey = Entity.EntityDimKey
INNER JOIN DWDAtaMart.dbo.WorkItemDimvw WI
ON I.EntityDimKey = WI.EntityDimKey
LEFT OUTER JOIN
DWDataMart.dbo.IncidentStatusvw AS StatusEnum
ON StatusEnum.IncidentStatusId = I.Status_IncidentStatusId
LEFT OUTER JOIN
DWDataMart.dbo.DisplayStringDimvw StatusDS
ON StatusEnum.EnumTypeId=StatusDS.BaseManagedEntityId
AND StatusDS.LanguageCode = 'ENU'
Left Outer Join DWDataMart.dbo.IncidentTierQueuesvw TierQueueEnum
ON I.TierQueue_IncidentTierQueuesId = TierQueueEnum.IncidentTierQueuesId
Left Outer Join DWDataMart.dbo.DisplayStringDimvw TierQueueEnumDS
ON TierQueueEnum.EnumTypeId = TierQueueEnumDS.BaseManagedEntityId
AND TierQueueEnumDS.LanguageCode = 'ENU'
WHERE StatusDS.DisplayName IN ('Closed' , 'Resolved') and (TierQueueEnumDS.DisplayName IN (@Supportgroup)) and (DATEPART(year,I.ResolvedDate) = @Year)
) IncidentResolved
group by IncidentResolved.ranges) a
FULL JOIN
(SELECT IncidentResolved.ranges AS 'ResolvedMonth', count(*) AS 'IncidentResolved' FROM
(
SELECT CASE
WHEN DATEPART(month,I.ResolvedDate) = '1' then '1'
WHEN DATEPART(month,I.ResolvedDate) = '2' then '2'
WHEN DATEPART(month,I.ResolvedDate) = '3' then '3'
WHEN DATEPART(month,I.ResolvedDate) = '4' then '4'
WHEN DATEPART(month,I.ResolvedDate) = '5' then '5'
WHEN DATEPART(month,I.ResolvedDate) = '6' then '6'
WHEN DATEPART(month,I.ResolvedDate) = '7' then '7'
WHEN DATEPART(month,I.ResolvedDate) = '8' then '8'
WHEN DATEPART(month,I.ResolvedDate) = '9' then '9'
WHEN DATEPART(month,I.ResolvedDate) = '10' then '10'
WHEN DATEPART(month,I.ResolvedDate) = '11' then '11'
WHEN DATEPART(month,I.ResolvedDate) = '12' then '12'
ELSE 'x'
END AS Ranges
FROM
DWDataMart.dbo.IncidentDimvw I
INNER JOIN DWDAtaMart.dbo.EntityDimvw Entity
ON I.EntityDimKey = Entity.EntityDimKey
INNER JOIN DWDAtaMart.dbo.WorkItemDimvw WI
ON I.EntityDimKey = WI.EntityDimKey
LEFT OUTER JOIN
DWDataMart.dbo.IncidentStatusvw AS StatusEnum
ON StatusEnum.IncidentStatusId = I.Status_IncidentStatusId
LEFT OUTER JOIN
DWDataMart.dbo.DisplayStringDimvw StatusDS
ON StatusEnum.EnumTypeId=StatusDS.BaseManagedEntityId
AND StatusDS.LanguageCode = 'ENU'
Left Outer Join DWDataMart.dbo.IncidentTierQueuesvw TierQueueEnum
ON I.TierQueue_IncidentTierQueuesId = TierQueueEnum.IncidentTierQueuesId
Left Outer Join DWDataMart.dbo.DisplayStringDimvw TierQueueEnumDS
ON TierQueueEnum.EnumTypeId = TierQueueEnumDS.BaseManagedEntityId
AND TierQueueEnumDS.LanguageCode = 'ENU'
WHERE StatusDS.DisplayName IN ('Closed' , 'Resolved') and (TierQueueEnumDS.DisplayName IN (@Supportgroup)) and (DATEPART(year,I.ResolvedDate) = @Year) and ((DATEPART(day,I.CreatedDate)) = (DATEPART(day,I.ResolvedDate)))
) IncidentResolved
group by IncidentResolved.ranges) b
on a.ResolvedMonth = b.ResolvedMonth
Order by ABS(a.ResolvedMonth)
The output will be like below, when you using it in Excel.
Mary Christmas
