Home Analytics

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

Stephane_BouillonStephane_Bouillon Customer Adept IT Monkey ✭✭

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_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];
         ,NumAssigns.NumberOfAssigns AS 'AssignedToUser_NumberOfAssigns'
    FROM [dbo].[IncidentDimvw] IR (nolock)
    INNER JOIN [dbo].[WorkItemDimvw] WI (nolock)
    ON WI.EntityDimKey = IR.EntityDimKey
      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'
    FROM [dbo].[IncidentTierQueueDurationFactvw] (nolock)
    Group By [IncidentDimKey]
    ORDER BY CountColumn DESC

    Good Luck young one!
Sign In or Register to comment.