How many Incident Requests are resolved within the same day?

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. http://blog.coretech.dk
	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.

image

image

Mary Christmas


Leave a Reply