/bundles/itninjaweb/img/Breadcrumb_cap_w.png
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   [ - ] Hide 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.
Answer this question or Comment on this question for clarity

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