Miscellaneous : Service Desk report for number of tickets by catagory, By month and YTD

Service Desk report for number of tickets by catagory, By month and YTD

We use categories as an owner only option on our tickets. I need to generate a report that will show number of tickets opened in each category and sub-category. This report would need to be by month and YTD cumulative for each count. Thank you in advance for your assistance with this.

0 Comments [ + ] Show comments

Answers (1)

Answer Summary:

Posted by: h2opolo25 9 years ago

This should get you started...

SELECT HD_QUEUE.NAME as Queue, HD_CATEGORY.NAME as Category,

count(HD_TICKET.ID) as 'Total Closed Tickets'

FROM HD_TICKET

JOIN HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID)

LEFT JOIN HD_CATEGORY ON (HD_TICKET.HD_CATEGORY_ID=HD_CATEGORY.ID)

left join HD_QUEUE on HD_QUEUE.ID = HD_TICKET.HD_QUEUE_ID

WHERE ((HD_STATUS.STATE like '%closed%')

AND (HD_TICKET.TIME_CLOSED > utc_timestamp() - interval 1 month)

)

GROUP BY HD_TICKET.HD_CATEGORY_ID DESC

order by count(HD_TICKET.ID)

Comments:

Thanks. does this report show only one month back or YTD? Also is this only showing closed tickerts? Just a newbie trying to get a feel for reports and what is entailed in this one. - comjam 9 years ago
- This is a baseline report showing closed tickets in the past month.
  I suggest you learn MySQL since it will be really useful for creating the type of reports you want.
  
  For example if you look at the code you can change the following line...
  AND (HD_TICKET.TIME_CLOSED > utc_timestamp() - interval 1 month) to AND HD_TICKET.TIMECLOSED > "2015-01-01 00:00:00" and this would give you all closed tickets from January 1st to today. - h2opolo25 9 years ago

Answer this question

Posted by:

View more:

Miscellaneous Questions

question