/build/static/layout/Breadcrumb_cap_w.png

Report amount of time ticket stalled

I'm trying to calculate the total amount of time spent on a ticket.  I want to include stalled tickets, but can't seem to find where the start/stop times are for when a ticket is in a stalled state.  This is the query I have so far.  HD_TICKET.CREATED AND HD_TICKET_CLOSED only show the last action.

Select count(HD_TICKET.ID) as Total_Closed from HD_TICKET 

   left join HD_QUEUE on HD_TICKET.HD_QUEUE_ID = HD_QUEUE.ID 

   left join HD_STATUS on HD_STATUS_ID = HD_STATUS.ID 

   where HD_QUEUE.ID = '3' 

      and HD_STATUS.STATE = 'closed' 

  and HD_TICKET.CREATED BETWEEN DATE_SUB(CURDATE(), INTERVAL 4 week) and CURDATE()

      and DATEDIFF(HD_TICKET.TIME_CLOSED, HD_TICKET.CREATED) < 2

) as 'Total Closed 1 day or less',


0 Comments   [ + ] Show comments

Answers (1)

Posted by: Hobbsy 2 months ago
Red Belt
0

Well that is going to be a challenge for you, for the last decade, since KACE introduced SLA’s, working time etc I have been asking them to provide a method for stopping the clock so that ticket duration can be measured more accurately. Please feel free to join the chorus!!

the only way you can do this is to record the time stamps when the ticket goes into a hold state, status change and when it comes out, and then calculate that time and subtract from the overall ticket duration.

That activity can be found in the ticket Change table, but this only works if the ticket is on hold once. 

The alternative is to implement some old SLA code which we use occasionally that has a counter that adds up the time on hold, if you would like a copy of that to try, please email me.

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ