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