/build/static/layout/Breadcrumb_cap_w.png

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
  • 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 - Druis 9 years ago

Answers (1)

Answer Summary:
Posted by: chucksteel 9 years ago
Red Belt
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


Comments:

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