KBOX Reporting Tickets - How do I generate a report showing total tickets created for the week ? I need the amount not the actual tickets
KBOX Reporting Tickets : How do I generate a report showing total tickets created for the week ? I need the amount not the actual tickets
1 Comment
[ + ] Show comment
Answers (1)
Answer Summary:
Please log in to answer
Posted by:
chucksteel
10 years ago
To generate a count of rows in MySQL you select the count of one of the fields, for tickets I normally use the ID, and then group by a field that you want to group things by. For instance, if you want the total tickets per queue you would group by HD_QUEUE_ID.
SELECT HD_QUEUE_ID, count(ID) FROM ORG1.HD_TICKET GROUP BY HD_QUEUE_ID;
This query will show each queue ID and the number of tickets total. To limit this to tickets from the past week add a where clause:
SELECT HD_QUEUE_ID, count(ID) FROM ORG1.HD_TICKET
WHERE CREATED > DATE_SUB(NOW(), INTERVAL 7 DAY)
GROUP BY HD_QUEUE_ID
To make this report a little more user friendly you might want to join to the HD_QUEUE table to get the queue names:
SELECT HD_QUEUE.NAME, count(HD_TICKET.ID) FROM ORG1.HD_TICKET
JOIN HD_QUEUE on HD_QUEUE.ID = HD_TICKET.HD_QUEUE_ID
WHERE CREATED > DATE_SUB(NOW(), INTERVAL 7 DAY)
GROUP BY HD_QUEUE_ID
Comments:
-
Thank you so much !!! It worked perfectly - cpace@oneitp.com 10 years ago
Select Count(HD_TICKET.ID) AS ID FROM HD_TICKET WHERE (HD_TICKET.HD_QUEUE_ID = 6) AND (((TIMESTAMP(HD_TICKET.CREATED) <= NOW() AND TIMESTAMP(HD_TICKET.CREATED) > DATE_SUB(NOW(),INTERVAL 7 DAY))))
You'll need to change the Queue number for your needs - Druis 10 years ago