SQL Query for average resolution times

Answers

• Options

  james_kleinschnitz Cireson Dev, Product Owner Advanced IT Monkey ✭✭✭

  October 2016

  @timi_kincaid may be able to help.
  

  0
• Options

  timi_kincaid Cireson Dev IT Monkey ✭

  October 2016

  Hi Eivind,

  Currently our schema does not support the time and date dimensions you would traditionally use in a data warehouse to exclude non-business hours and non-workdays. We only have the concept that Service Manager gives us of created date and resolved date. If you would like to see time and date dimensions added to our schema, I strongly encourage you to submit a feature request. For now, the best we can do with our schema is something like this:

  DECLARE @Month int = DATEPART(mm, GetDate()), @Day int = DATEPART(dd,GETDATE()), @Year int = DATEPART(yyyy, GETDATE())
  SELECT TOP 25 ISNULL(d.DisplayStringOverride, DisplayString) as SupportGroup, AVG(DATEDIFF(mi,Created, ResolvedDate)) as AverageResolutionTime
  FROM WorkItem wi
  INNER JOIN CI$User u ON wi.AssignedUserId = u.Id
  INNER JOIN GroupMembership_CI$DomainGroup_CI$User ugrp ON u.Id = ugrp.UserId
  INNER JOIN SupportGroupMapping_CI$DomainGroup_Enumeration sgrp ON ugrp.DomainGroupId = sgrp.DomainGroupId
  INNER JOIN DisplayString d ON sgrp.EnumerationId = d.ElementID
  WHERE ResolvedDate IS NOT NULL
  AND Created IS NOT NULL
  AND ClassId = 'A604B942-4C7B-2FB2-28DC-61DC6F465C68' --Incident
  AND DATEPART(yyyy, ResolvedDate) = @Year
  AND DATEPART(mm, ResolvedDate) = @Month
  AND DATEPART(dd, ResolvedDate) BETWEEN 1 AND @Day
  AND d.LocaleID = 'ENU'
  GROUP BY sgrp.DomainGroupId, d.DisplayStringOverride, d.DisplayString
  ORDER BY AverageResolutionTime ASC

  2
• Options

  Josh_Gilliam Customer IT Monkey ✭

  July 2017

  Does this query display the avg reso times in minutes?

  0
• Options

  Moritz_Frei Customer IT Monkey ✭

  November 2021

  Hello,

  you can use this function, it also excludes time outside business hours.

  
  

  -- =============================================

  -- Author:   Baran Kaynak (modified by Frei 2021-10-11)

  -- Create date: 14.03.2011

  -- Description: Working Time 7-17

  -- =============================================

  FUNCTION [dbo].[WorkTime] 

  (

    @StartDate DATETIME,

    @FinishDate DATETIME

  )

  RETURNS BIGINT

  AS

  BEGIN

    DECLARE @Temp BIGINT

    SET @Temp=0

  
  

    DECLARE @FirstDay DATE

    SET @FirstDay = CONVERT(DATE, @StartDate, 112)

  
  

    DECLARE @LastDay DATE

    SET @LastDay = CONVERT(DATE, @FinishDate, 112)

  
  

    DECLARE @StartTime TIME

    SET @StartTime = CONVERT(TIME, @StartDate)

  
  

    DECLARE @FinishTime TIME

    SET @FinishTime = CONVERT(TIME, @FinishDate)

  
  

    DECLARE @WorkStart TIME

    SET @WorkStart = '07:00'

  
  

    DECLARE @WorkFinish TIME

    SET @WorkFinish = '17:00'

  
  

    DECLARE @DailyWorkTime BIGINT

    SET @DailyWorkTime = DATEDIFF(MINUTE, @WorkStart, @WorkFinish)

  
  

    IF (@StartTime<@WorkStart)

    BEGIN

      SET @StartTime = @WorkStart

    END

    IF (@FinishTime>@WorkFinish)

    BEGIN

      SET @FinishTime=@WorkFinish

    END

    IF (@FinishTime<@WorkStart)

    BEGIN

      SET @FinishTime=@WorkStart

    END

    IF (@StartTime>@WorkFinish)

    BEGIN

      SET @StartTime = @WorkFinish

    END

  
  

    DECLARE @CurrentDate DATE

    SET @CurrentDate = @FirstDay

    DECLARE @LastDate DATE

    SET @LastDate = @LastDay

  
  

    WHILE(@CurrentDate<=@LastDate)

    BEGIN    

      IF (DATEPART(dw, @CurrentDate)!=1 AND DATEPART(dw, @CurrentDate)!=7)

      BEGIN

        IF (@CurrentDate!=@FirstDay) AND (@CurrentDate!=@LastDay)

        BEGIN

          SET @Temp = @Temp + @DailyWorkTime

        END

        --IF it starts at startdate and it finishes not this date find diff between work finish and start as minutes

        ELSE IF (@CurrentDate=@FirstDay) AND (@CurrentDate!=@LastDay)

        BEGIN

          SET @Temp = @Temp + DATEDIFF(MINUTE, @StartTime, @WorkFinish)

        END

  
  

        ELSE IF (@CurrentDate!=@FirstDay) AND (@CurrentDate=@LastDay)

        BEGIN

          SET @Temp = @Temp + DATEDIFF(MINUTE, @WorkStart, @FinishTime)

        END

        --IF it starts and finishes in the same date

        ELSE IF (@CurrentDate=@FirstDay) AND (@CurrentDate=@LastDay)

        BEGIN

          SET @Temp = DATEDIFF(MINUTE, @StartTime, @FinishTime)

        END

      END

      SET @CurrentDate = DATEADD(day, 1, @CurrentDate)

    END

  
  

    -- Return the result of the function

    IF @Temp<0

    BEGIN

      SET @Temp=0

    END

    RETURN @Temp

  
  

  END

  0