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:
Cancel
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.
Please log in to comment

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!
Please log in to comment

Answers

Answer this question or Comment on this question for clarity