/build/static/layout/Breadcrumb_cap_w.png

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

Answers (1)

Answer Summary:
Posted by: Hobbsy 9 years ago
Red Belt
1

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