Good afternoon! I was wondering if I might get some help creating a report that has the following characteristics:
-Collects the number of tickets for 2 categories - Access::LAN Locked & Access::LAN Expired
-Segments the count of the tickets by month created, where it goes back to the beginning of the year
-Has a total count of tickets at the bottom of the report
for instance:

1
72
Access::LAN ExpiredSeptember 2014
2
38
Access::LAN LockedSeptember 2014
3
24
Access::LAN ExpiredOctober 2014
4
58
Access::LAN Locked
October 2014













This report (provided by another user on this site, thanks!) is quite close to what I am looking for, but again, I'd like to be able to narrow it down by specific categories and have it go back a full year with each month showing the number of tickets for that category and have a total count at the bottom.

SELECT
COUNT(HD_TICKET.ID) AS NUMBER_TICKETS,
HD_CATEGORY.NAME AS CATEGORY,
date_format(HD_TICKET.CREATED, '%M %Y') AS DATE_CREATED
FROM
HD_TICKET
JOIN
HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID)
WHERE
(HD_TICKET.HD_QUEUE_ID = 1)
AND (TIMESTAMP(HD_TICKET.CREATED) BETWEEN date_format(NOW() - INTERVAL 1 MONTH, '%Y-%m-01')
AND last_day(NOW() - INTERVAL 1 MONTH))
GROUP BY DATE_CREATED , CATEGORY

Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

0
This should do it. I changed the interval of the created tickets from 1 month to 1 year and added filters to only look for the two categories you are interested in.


SELECT
COUNT(HD_TICKET.ID) AS NUMBER_TICKETS,
HD_CATEGORY.NAME AS CATEGORY,
date_format(HD_TICKET.CREATED, '%M %Y') AS DATE_CREATED
FROM
HD_TICKET
JOIN
HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID)
WHERE
(HD_TICKET.HD_QUEUE_ID = 1)
and HD_CATEGORY.NAME = ('Access::LAN Locked')
or HD_CATEGORY.NAME = ('Access:LAN Expired')
AND (TIMESTAMP(HD_TICKET.CREATED) BETWEEN date_format(NOW() - INTERVAL 1 YEAR, '%Y-%m-01')
AND last_day(NOW() - INTERVAL 1 MONTH))
GROUP BY DATE_CREATED , CATEGORY
Answered 10/15/2014 by: h2opolo25
Red Belt

  • Thank you so very much!
    • Welcome, just noticed an error in the code.... there should be 2 colons in the second category:
      Access::LAN Expired
Please log in to comment
Answer this question or Comment on this question for clarity

Share