Home Analyst Portal

SQL Query for average resolution times

Eivind_OsbakEivind_Osbak Customer Advanced IT Monkey ✭✭✭

Hi.

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.

Eivind

Answers

  • 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 ✭

    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 @[email protected]

      END

      IF (@FinishTime<@WorkStart)

      BEGIN

        SET @[email protected]

      END

      IF (@StartTime>@WorkFinish)

      BEGIN

        SET @StartTime = @WorkFinish

      END


      DECLARE @CurrentDate DATE

      SET @CurrentDate = @FirstDay

      DECLARE @LastDate DATE

      SET @LastDate = @LastDay


      WHILE(@CurrentDate<[email protected])

      BEGIN    

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

        BEGIN

          IF (@[email protected]) AND (@[email protected])

          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 (@[email protected]) AND (@[email protected])

          BEGIN

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

          END


          ELSE IF (@[email protected]) AND (@[email protected])

          BEGIN

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

          END

          --IF it starts and finishes in the same date

          ELSE IF (@[email protected]) AND (@[email protected])

          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

Sign In or Register to comment.