How to find the incidents with the most re-assignments ?

Stephane_Bouillon

Hi,

I'd like to write a SQL query to find incidents that have the most number of times been re-assigned to different agents or support groups, as it would indicate a problem for which no clear owner exists. Does anybody have a sample ?

Conner_Wood

Muwhahahahahah, Muwhauhauhahauhauhauha!!!!!!!


Now to sum it up:

• what you'd use for LIVE == the history log in the LIVE ServiceManager DB
• what you'd use for ARCHIVE == The Enumeration Duration and Relationship Tables (which actually is formed via the LIVE history log, that's how it all meshes together into a big blob of chaos!)

So for the DWDataMart (the Data Warehouse ARCHIVE), I've already made the following:

Assigned To Counter

USE [DWDataMart];

SELECT IR.Id
     ,IR.IncidentDimKey
     ,WI.WorkItemDimKey
     ,NumAssigns.NumberOfAssigns AS 'AssignedToUser_NumberOfAssigns'
FROM [dbo].[IncidentDimvw] IR (nolock)

INNER JOIN [dbo].[WorkItemDimvw] WI (nolock)
ON WI.EntityDimKey = IR.EntityDimKey

INNER JOIN 
(
  SELECT [WorkItemDimKey],COUNT(*) AS 'NumberOfAssigns'
  FROM [dbo].[WorkItemAssignedToUserFactvw] (nolock)
  GROUP BY [WorkItemDimKey]
  HAVING COUNT(*) > 1
) AS NumAssigns ON NumAssigns.WorkItemDimKey = WI.WorkItemDimKey

ORDER BY NumAssigns.NumberOfAssigns DESC

Support Group Counter

USE [DWDataMart];

SELECT COUNT([IncidentTierQueuesId]) AS 'CountColumn'
     ,[IncidentDimKey]
FROM [dbo].[IncidentTierQueueDurationFactvw] (nolock)
Group By [IncidentDimKey]
ORDER BY CountColumn DESC

Good Luck young one!