Simple service desk report for closed ticket by month
I am looking for a simple SQL query to find tickets closed within the last 7 days. Currently, I have a very simple report for tickets opened in the last 7 days. The SQL code is posted below. Does anyone know the code similar that gets the closed ticket results for the past 7 days?
SELECT title, created FROM HD_TICKET H
WHERE created > (DATE_SUB(CURDATE(), INTERVAL 7 DAY))
ORDER BY created;
I do know that there is a canned report for this, but I am hoping to section this off by 2 different queues. I can edit the SQL above since it is easy to figure out, but the canned report is too complex for me to get.
1 Comment
[ + ] Show comment
-
You already have an answer but I'd like to suggest that you setup a tool like MySQL Workbench. This will allow you to look at the database tables and will help make custom reports. - chucksteel 8 years ago
Answers (1)
Answer Summary:
Please log in to answer
Posted by:
Hobbsy
8 years ago
Top Answer
You can build out this report quite easily using the wizard and then tweak the SQL
Try the following
SELECT
HD_TICKET.ID,
HD_TICKET.TIME_CLOSED,
HD_TICKET.TITLE
FROM HD_TICKET
WHERE
(HD_TICKET.HD_QUEUE_ID = 9)
AND (((TIMESTAMP(HD_TICKET.TIME_CLOSED) <= NOW()
AND TIMESTAMP(HD_TICKET.TIME_CLOSED) > DATE_SUB(NOW(),INTERVAL 7 DAY))))
ORDER BY ID
Comments:
-
That worked like a charm! Thank you for your help! - zgillette 8 years ago
