/build/static/layout/Breadcrumb_cap_w.png

Best Practices Question


Simple service desk report for closed ticket by month

05/14/2015 969 views
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.


Answer Summary:
1 Comment   [ + ] Show comment

Comments

  • 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.

Answer Chosen by the Author

1
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
Answered 05/15/2015 by: Hobbsy
Red Belt

  • That worked like a charm! Thank you for your help!
 
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