How can i add a "between 2 dates" option to a report
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
Answers (1)
Please log in to answer
Posted by:
chucksteel
11 years ago
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.
Comments:
-
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 - dave1kelsey 11 years ago-
Try enclosing the last line in ( ).
and (HD_TICKET.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL 31 DAY)) ORDER BY CATEGORY - dugullett 11 years ago -
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. - chucksteel 11 years ago
-
Still got the same issue im afraid. - dave1kelsey 11 years ago
-
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 - dave1kelsey 11 years ago-
Dave,
If you are still working on this, can you post the SQL you have now for a review? - scottlutz 11 years ago
