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

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.
  • 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
  • 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 )
Please log in to comment

There are no answers at this time

Answers

Answer this question or Comment on this question for clarity
Admin Script Editor
Admin Script Editor is an integrated scripting environment available free here at ITNinja

Share