/build/static/layout/Breadcrumb_cap_w.png

how to see the total of close tickets in queue

I am trying to see if I can pull a report showing the total of tickets closed in all of my queues.



0 Comments   [ + ] Show comments

Answers (2)

Posted by: Hobbsy 3 years ago
Red Belt
0

SO you should be able to pull that from a standard report using the wizard, I would say remember that the date criteria needs to be "Where close date is equal to today" but you may need to edit the SQL to include multiple Queue ID's if you want multiple queues.


If you look at a prebaked report the SQL looks like this so you could just adjust the date criteria in yellow. If it doesn't show the queues you want you could add them into the WHERE Statement


select HD_TICKET.ID, 

       HD_TICKET.TITLE ,

       HD_TICKET.DUE_DATE ,

       HD_TICKET.TIME_CLOSED as TIME_CLOSED,

       CONCAT(IF(TIME_TO_SEC(HD_TICKET.TIME_CLOSED) >= TIME_TO_SEC(HD_TICKET.TIME_OPENED),

              TO_DAYS(HD_TICKET.TIME_CLOSED) - TO_DAYS(HD_TICKET.TIME_OPENED),

              TO_DAYS(HD_TICKET.TIME_CLOSED) - TO_DAYS(HD_TICKET.TIME_OPENED) - 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.TIME_OPENED))),

              '%kh %im')) AS TIME_TO_CLOSE,

       HD_PRIORITY.NAME as PRIORITY, 

       HD_CATEGORY.NAME as CATEGORY, 

       HD_STATUS.NAME as STATUS, 

       HD_IMPACT.NAME as IMPACT,

       MACHINE.NAME as MACHINE_NAME,

       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_NAME

from HD_TICKET

left join HD_CATEGORY on HD_CATEGORY_ID = HD_CATEGORY.ID

left join HD_STATUS on HD_STATUS_ID = HD_STATUS.ID

left join HD_PRIORITY on HD_PRIORITY_ID = HD_PRIORITY.ID

left join HD_IMPACT on HD_IMPACT_ID = HD_IMPACT.ID

left join MACHINE on HD_TICKET.MACHINE_ID = MACHINE.ID

where HD_STATUS.STATE = 'closed' and HD_TICKET.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL 7 DAY)

order by OWNER_NAME, HD_PRIORITY.ORDINAL, HD_CATEGORY.ORDINAL, HD_STATUS.ORDINAL, HD_IMPACT.ORDINAL


Posted by: jasmeet10 3 years ago
White Belt
0

hello, I am also facing same problem then i took the help from youtube then I get the solution.here is the solution


SELECT queue.name AS 'Queue', COUNT(ticket.id)FROM ticket queue ON ticket.queue_id = queue.idWHERE ticket.create_by != 1AND DATE(ticket.create_time) >= DATE_SUB(CUR_DATE(),INTERVAL 1 day)AND ticket.queue_id IN ( SELECT id FROM queue WHERE valid_id=1 ORDER BY name)GROUP BY queue.name;

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ