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