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

Stephane_BouillonStephane_Bouillon Customer IT Monkey ✭
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 ?

Answers

  • Conner_WoodConner_Wood Customer Advanced IT Monkey ✭✭✭
    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!
Sign In or Register to comment.