K1000 - Get a Report of Total Time that a Ticket is Open?
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.
0 Comments
[ + ] Show comments
Answers (2)
Answer Summary:
Please log in to answer
Posted by:
chucksteel
9 years ago
Top Answer
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.
Comments:
-
That was exactly what I was looking for. Thank you very much! - evmorr12 9 years ago
Posted by:
Jbr32
9 years ago
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
Comments:
-
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. - Jbr32 9 years ago
-
Is there a way to add some query of the SLA table to get the true business hours time a ticket was open? - jharrell 8 years ago
-
I wish I knew more, but how would I fix this query if it's not pulling in tickets over 1,000 hours?
Ideally I'd like an average per ticket owner, too. This report's data seemed to be skewed due to using time_opened vs. time_created: https://support.software.dell.com/kb/111916 - Truth0r 7 years ago
