/bundles/itninjaweb/img/Breadcrumb_cap_w.png
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   [ - ] Hide 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


Answers

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
Answer this question or Comment on this question for clarity

Answers