/build/static/layout/Breadcrumb_cap_w.png

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
  • Do you want these counts for a single queue or broken down by queue? Also, are you looking for tickets created (really easy), closed (really easy), touched (moderately difficult), or a combination of those (could be difficult)?

    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 7 years ago
  • Give this a try. You might just have to change the following line to your queue ID: HD_TICKET.HD_QUEUE_ID = 20 <== change


    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 7 years ago
  • This might work as well. Just change the INTERVAL 7 DAY LINE TO 31

    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 7 years ago

Answers (0)

Be the first to answer this question

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

View more:

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ