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.


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

edit: added code tags on your SQL
1 Comment   [ + ] Show Comment

Comments

  • 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.
Please log in to comment

Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

Answers

0
The work information lives in HD_WORK and has the same 1:N relationship to HD_TICKET that HD_TICKET_CHANGE has.

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
...
Answered 02/03/2012 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
Answer this question or Comment on this question for clarity