Home Analytics

Average Ticket Resolution Time (SQL Query)

Moritz_FreiMoritz_Frei Customer IT Monkey ✭
edited November 2021 in Analytics

Hello,

has anyone a query that gets the average resolution time of all incidents? I already got one but i need to exclude the non business hours and weekends.


SELECT 

AVG(Cast(DateDiff(minute, [Created], [ResolvedDate]) as bigint)) as AverageDuration

FROM [ServiceManagement].[dbo].[WorkItem]

 WHERE ResolvedDate not like 'NULL'


Thank you!

Best Answer

  • Moritz_FreiMoritz_Frei Customer IT Monkey ✭
    Accepted Answer

    We now use this function:

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

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

Answers

  • Moritz_FreiMoritz_Frei Customer IT Monkey ✭
    Accepted Answer

    We now use this function:

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

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