SQL Query for average resolution times

Eivind_Osbak

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

james_kleinschnitz

@timi_kincaid may be able to help.

timi_kincaid

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_Gilliam

Does this query display the avg reso times in minutes?

Moritz_Frei

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