Time Takes to Close Tickets by Week and Month
Okay I have my report almost completed on how I want it, Thanks to this forum I have created this so far, which is not really that intense, but I am proud of myself.
I am wanting to add 'worked logged' to this and have not been successfully. Can someone let me know where the 'work time' lives?
Then after that is added I was wanting this to do averages for me, ie User A had X amount of tickets and took him an average of Y time to complete.
and Team 1 with Users A, B, and C had a combined X amount of tickets and took them and average of Z time to complete.
edit: added code tags on your SQL
I am wanting to add 'worked logged' to this and have not been successfully. Can someone let me know where the 'work time' lives?
Then after that is added I was wanting this to do averages for me, ie User A had X amount of tickets and took him an average of Y time to complete.
and Team 1 with Users A, B, and C had a combined X amount of tickets and took them and average of Z time to complete.
select HD_TICKET.ID,
HD_TICKET.CREATED AS CREATED,
HD_TICKET.TIME_CLOSED AS CLOSED,
CONCAT(IF(TIME_TO_SEC(HD_TICKET.TIME_CLOSED) >= TIME_TO_SEC(HD_TICKET.CREATED),
TO_DAYS(HD_TICKET.TIME_CLOSED) - TO_DAYS(HD_TICKET.CREATED),
TO_DAYS(HD_TICKET.TIME_CLOSED) - TO_DAYS(HD_TICKET.CREATED) - 1), 'd ',
DATE_FORMAT(ADDTIME("2000-01-01 00:00:00",
SEC_TO_TIME(TIME_TO_SEC(HD_TICKET.TIME_CLOSED)-TIME_TO_SEC(HD_TICKET.CREATED))),
'%kh %im')) AS TIME_TO_CLOSE,
HD_PRIORITY.NAME as PRIORITY,
ifnull((select FULL_NAME from USER where HD_TICKET.OWNER_ID = USER.ID),' Unassigned') as OWNER_NAME,
(select FULL_NAME from USER where HD_TICKET.SUBMITTER_ID = USER.ID) as SUBMITTER
from HD_TICKET
left join HD_PRIORITY on HD_PRIORITY_ID = HD_PRIORITY.ID
where HD_TICKET.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL 31 DAY)
order by OWNER_NAME, HD_PRIORITY.ORDINALedit: added code tags on your SQL
1 Comment
[ + ] Show comment
-
This is great, but how can I make this for specific queues versus a list of all users? We have several different queues for several different departments and really only need the information for one queue at a time. - ghubbard 8 years ago
Answers (1)
Please log in to answer
Posted by:
GillySpy
12 years ago
The work information lives in HD_WORK and has the same 1:N relationship to HD_TICKET that HD_TICKET_CHANGE has.
something like:
something like:
select
...
ROUND((time_to_sec(time(stop)) - time_to_sec(time(start)))/3600.0 +
ADJUSTMENT_HOURS, 2) as HOURS_WORKED
...
HD_TICKET JOIN HD_WORK W ON W.HD_TICKET_ID.HD_TICKET.ID
...
Rating comments in this legacy AppDeploy message board thread won't reorder them,so that the conversation will remain readable.
