KBOX Reporting Tickets : How do I generate a report showing total tickets created for the week ? I need the amount not the actual tickets
Answer Summary:
Cancel
1 Comment   [ + ] Show Comment

Comments

  • Try this:
    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
Please log in to comment

Answers

0
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

Answered 10/28/2014 by: chucksteel
Red Belt

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