Report Request - # of Tickets per category
I don't know much SQL and the report wizard does not seem to be able to cut it for me - I need a report that just provides me the raw numbers for the past 31 days of the number of tickets per category. So for example, I'd want to see something like:
Hardware::Printers - 30
Hardware::Monitors - 23
etc...
I don't care what the ticket status is, just that it is every ticket within the last 31 days.
Other reports that would be helpful in this same style would be # of tickets in each IMPACT type and # of tickets of each priority type (each for the past 31 days).
Has anyone done this already or can assist in writing/creating the report? Using the wizard, it breaks it out with a category as a heading, then lists all the tickets with ticket # and detail under that heading. I have to manually count each one to add them up.
3 Comments
[ + ] Show comments
Answers (0)
Please log in to answer
Be the first to answer this question
Since you already have a wizard report pulling the records you want included, that's a good place to start. if you could include that here, I can take a quick look. - grayematter 8 years ago
Select
HD_CATEGORY.NAME As CATEGORY, COUNT(*)As Tickets
From
HD_TICKET Join
HD_CATEGORY
On HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID
Where
HD_TICKET.HD_QUEUE_ID = 20 And
Timestamp(HD_TICKET.CREATED) <= Now() And
Timestamp(HD_TICKET.CREATED) > Date_Sub(Now(), Interval 31 Day)
Order By
CATEGORY - nshah 8 years ago
SELECT HD_CATEGORY.NAME AS 'Category', COUNT(HD_TICKET.TITLE) as 'Count of Tickets'
FROM HD_TICKET, HD_CATEGORY
WHERE HD_TICKET.HD_CATEGORY_ID = HD_CATEGORY.ID AND (HD_TICKET.HD_QUEUE_ID = 1) AND HD_TICKET.CREATED >= ( CURDATE( ) - INTERVAL 7 DAY ) AND (HD_TICKET.TIME_OPENED > 0)
group by CATEGORY
UNION
Select 'TOTAL', count(HD_TICKET.ID) as mytotalcount from HD_TICKET where HD_TICKET.CREATED >= ( CURDATE( ) - INTERVAL 7 DAY ) - nshah 8 years ago