Home Analyst Portal

SQL Query for average resolution times

Eivind_OsbakEivind_Osbak Customer Advanced IT Monkey ✭✭✭


Does anyone know a sql query to display average resolutiontime for incidents of current month or week grouped on supportgroup in the SQL Graph on a new page (6.0.2)?

Should exclude time outside business hours.



  • james_kleinschnitzjames_kleinschnitz Cireson Dev, Product Owner Advanced IT Monkey ✭✭✭
    @timi_kincaid may be able to help.
  • timi_kincaidtimi_kincaid Cireson Dev IT Monkey ✭

    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

  • Josh_GilliamJosh_Gilliam Customer IT Monkey ✭
    Does this query display the avg reso times in minutes?
  • Moritz_FreiMoritz_Frei Customer IT Monkey ✭


    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






      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)


        SET @StartTime = @WorkStart


      IF (@FinishTime>@WorkFinish)


        SET @FinishTime=@WorkFinish


      IF (@FinishTime<@WorkStart)


        SET @FinishTime=@WorkStart


      IF (@StartTime>@WorkFinish)


        SET @StartTime = @WorkFinish


      DECLARE @CurrentDate DATE

      SET @CurrentDate = @FirstDay

      DECLARE @LastDate DATE

      SET @LastDate = @LastDay



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


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


            SET @Temp = @Temp + @DailyWorkTime


          --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)


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


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


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


          --IF it starts and finishes in the same date

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


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



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


      -- Return the result of the function

      IF @Temp<0


        SET @Temp=0


      RETURN @Temp


Sign In or Register to comment.