I need to pull a quarterly report and show the total numbers of tickets....broken down by  each category and the sub categories.....

This is an example of the queue for hardware categories....Thanks for the help

I dont have any experience with SQL so any help would be great.....

Monthly report would also be ok.....

 

 

Category Values Toggle help text

Name

 Sort table by column

Default Owner

 Sort table by column

CC List

 Sort table by column

User Settable

 Sort table by column
Add item
Hardware::AV Equipment Unassigned   true Edit itemRemove itemDrag item up or down
Hardware::Equipment Move Unassigned   true Edit itemRemove itemDrag item up or down
Hardware::Server Room Unassigned   true Edit itemRemove itemDrag item up or down
Hardware::Computer Unassigned   true Edit itemRemove itemDrag item up or down
Hardware::Printer Unassigned   true Edit itemRemove itemDrag item up or down
Hardware Unassigned
Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Community Chosen Answer

4

The following query returns for the previous three months, which is a quarter. You should theoretically be able to use the QUARTER function in MySQL to make the more elegant, but this should work:

 select HD_CATEGORY.NAME, COUNT(HD_TICKET.ID)
from HD_TICKET
JOIN HD_CATEGORY on HD_TICKET.HD_CATEGORY_ID = HD_CATEGORY.ID
Where HD_TICKET.TIME_CLOSED BETWEEN date_format(NOW() - INTERVAL 4 MONTH, '%Y-%m-01') AND last_day(NOW() - INTERVAL 1 MONTH)
AND HD_TICKET.HD_QUEUE_ID = 2
GROUP BY HD_TICKET.HD_CATEGORY_ID

Be sure to change the HD_QUEUE_ID to match the queue that you are running the report for.

Answered 09/24/2013 by: chucksteel
Red Belt

  • not much experience with SQL....I get an error when I save the report.....I did change the name HD_Queue_ID to the name of the helpdesk queue
    Thanks
    cw
    • Don't use the queue's name, use the queue's ID.
  • Thank you I got it to work.....How can I change the code to search for range of dates that I would like to enter
    example 01/01/2013 to 3/31/2013
    • Change the HD_TICKET.TIME_CLOSED line to:
      HD_TICKET.CLOSED between '2013-01-01' AND '2013-03-31'

      For the first quarter of 2013 you could also do something like:
      QUARTER(HD_TICKET.CLOSED) = 1 and YEAR(HD_TICKET.CLOSED) = 2013
Please log in to comment

Answers

2

Im late to the party but meh,

select case
           when MONTH(T.CREATED) between 1 and 3 then '1st Quarter'
           when MONTH(T.CREATED) between 4 and 6 then '2nd Quarter'
           when MONTH(T.CREATED) between 7 and 9 then '3rd Quarter'
           when MONTH(T.CREATED) between 10 and 12 then '4th Quarter'
       end as QUARTER,
       C.NAME,
       COUNT(T.ID) as NUMBER_TICKETS
from HD_TICKET T
join HD_CATEGORY C on T.HD_CATEGORY_ID = C.ID
where T.HD_QUEUE_ID = 1
  and YEAR(T.CREATED) = YEAR(CURTIME())
group by QUARTER,
         T.HD_CATEGORY_ID
order by QUARTER asc
Answered 09/28/2013 by: jdornan
Red Belt

Please log in to comment
Answer this question or Comment on this question for clarity