Hi,

 

Im creating a report that shows the number of tickets created and groups them into categories using the basic report wizard, how can i now add the option to show only the past 31 days of entries?

0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

1

You can edit the SQL code of the report and add the following to the WHERE clauses:

 and HD_TICKET.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL 31 DAY)

This is based on the time the ticket was closed. If you'd like it based on tickets created in the past 31 days, change TIME_CLOSED to CREATED.

Answered 11/08/2012 by: chucksteel
Red Belt

  • Thanks, ive still got tickets from before 31 days ago, ive included the code in case ive done something obviously wrong. Could you see if it makes sense?

    SELECT HD_CATEGORY.NAME AS CATEGORY,HD_TICKET.CREATED,S.FULL_NAME AS SUBMITTER_NAME,HD_TICKET.TITLE,HD_TICKET.CUSTOM_FIELD_VALUE1 FROM HD_TICKET JOIN HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID) LEFT JOIN USER S ON (S.ID = HD_TICKET.SUBMITTER_ID) WHERE (HD_TICKET.HD_QUEUE_ID = 1) AND (HD_TICKET.APPROVAL = '') and HD_TICKET.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL 31 DAY) ORDER BY CATEGORY
    • Try enclosing the last line in ( ).

      and (HD_TICKET.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL 31 DAY)) ORDER BY CATEGORY
    • When I run that query in MySQL Workbench I only get tickets that were closed in the past 31 days. Some of them were created before that time period, but they were all closed since October 10th.
  • Still got the same issue im afraid.
  • Thanks for this everyone, is there an easy way to add the following:
    1) the number of items.
    2) the date the ticket was created

    Thanks
    • Dave,
      If you are still working on this, can you post the SQL you have now for a review?
Please log in to comment
Answer this question or Comment on this question for clarity
Admin Script Editor
Admin Script Editor is an integrated scripting environment available free here at ITNinja

Share