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

Answers

• Options

  Conner_Wood Customer Ninja IT Monkey ✭✭✭✭

  November 2017

  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!

  1