I'm new to K1000 and SQL, so sorry if this is an obvious question. I've checked around bit but I haven't found anything that quite applies to what I'm trying to do.

I need to create a report that will show the amount of time a ticket was open between when it was looked at by a technician to when it is closed. I thought I could do this through TIMESTAMPDIFF(HD_Ticket.TIME_CLOSED - HD_Ticket.TIME_OPENED) but I've only been getting errors. Is there any way to do this? Even seeing the total time from the ticket's creation to when it's initially closed would be useful.
Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answer Chosen by the Author

2
You should use the TIMEDIFF function with the TIME_CLOSED and CREATED columns:
TIMEDIFF(HD_TICKET.TIME_CLOSED, HD_TICKET.CREATED)

There isn't a reliable way to determine when a technician looked at a ticket since looking at a ticket doesn't always generate a ticket save event.

Answered 06/15/2015 by: chucksteel
Red Belt

  • That was exactly what I was looking for. Thank you very much!
Please log in to comment

Answers

1
Using Chuck's code from above, perhaps the following would work:

Select HDT.ID as TicketNumber, HDT.TITLE as TicketTitle, HDCAT.Name as CategoryName, HDQ.Name as QueueName, USR.FULL_NAME, TIMEDIFF(HDT.TIME_CLOSED, HDT.CREATED) as HoursOpen 
from HD_TICKET as HDT
join HD_STATUS as HDS on HDS.ID=HDT.HD_STATUS_ID 
join USER as USR on USR.ID=HDT.OWNER_ID
Join HD_CATEGORY as HDCAT on HDCAT.ID=HDT.HD_CATEGORY_ID
Join HD_QUEUE as HDQ on HDQ.ID=HDT.HD_QUEUE_ID
where HDS.NAME='Closed' 
order by HDT.ID desc limit 30
Answered 06/16/2015 by: Jbr32
Tenth Degree Black Belt

  • Note: if you take this data into a stats package, and then run an anova on it using the appropriate qstat you can determine if there is a statistical significant difference in time it takes one tech to close out a ticket vs other techs. Note that this difference many or many not mean anything, of course there are lots of other considerations; i.e. quality of service, level of sophistication, etc.
  • Is there a way to add some query of the SLA table to get the true business hours time a ticket was open?
Please log in to comment
Answer this question or Comment on this question for clarity