Here's a request from the CIO to see when the ticket loads are highest: Tickets created by month for last 3 years

Ideally it would be a graph but just the numbers by month could be easily put into an Excel spreadsheet to create a graph. We'd like to see how many tickets in all queues are created by month and then look back at which months have been the busiest in the past 3 years.

I know how to get the number of tickets for the last 30 days from the SQL below. I'd like to extend that to 1095 days (3 years) and have it break up the output by month.

SELECT COUNT(*) AS '# of Tickets Created' FROM HD_TICKET T
WHERE DATEDIFF(NOW(), CREATED) < 30
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

Answers

0
BTW, this tells me 36614 tickets were created in the past 3 years!

SELECT COUNT(*) AS '# of Tickets Created' FROM HD_TICKET T
WHERE DATEDIFF(NOW(), CREATED) < 1095
Answered 08/02/2010 by: RichB
Third Degree Green Belt

Please log in to comment
0
Try this? Change the 20071 represents Month 1 (Jan) 2007. If you want stats from 2006, you can change it to 20061. Im sure that there is a better way of optimizing it but this should do the trick for now.

SELECT HD_QUEUE.NAME AS Queue_Name, COUNT(T2.ID) AS 'Tickets_Opened', CONCAT(MONTHNAME(T2.CREATED),' ,YEAR(T2.CREATED)) as 'Month'
FROM HD_TICKET T2
JOIN HD_STATUS S ON (T2.HD_STATUS_ID = S.ID)
LEFT JOIN HD_QUEUE ON (T2.HD_QUEUE_ID = HD_QUEUE.ID)
WHERE (CONCAT(YEAR(T2.CREATED), MONTH(T2.CREATED)) > '20071')
GROUP BY HD_QUEUE.ID desc, CONCAT(YEAR(T2.CREATED), MONTH(T2.CREATED)) desc
Answered 08/02/2010 by: DContreras
Orange Belt

Please log in to comment
0
This is working great! Thanks!
Answered 08/04/2010 by: RichB
Third Degree Green Belt

Please log in to comment
0
I see you changed Quarter to Month and I also added some sorting. We have a bunch of queues and the most important one was at the bottom of the 7 page report. Now it is near the top and the month of August, our biggest one for tickets, pops to the top alphabetically to the first page too. The CIO was impressed and I owe it all to you guys supporting me here. [:)]

Here's the final result:

SELECT HD_QUEUE.NAME AS Queue_Name, COUNT(T2.ID) AS 'Tickets_Opened', CONCAT(MONTHNAME(T2.CREATED),' ,YEAR(T2.CREATED)) as 'Month'
FROM HD_TICKET T2
JOIN HD_STATUS S ON (T2.HD_STATUS_ID = S.ID)
LEFT JOIN HD_QUEUE ON (T2.HD_QUEUE_ID = HD_QUEUE.ID)
WHERE (CONCAT(YEAR(T2.CREATED), MONTH(T2.CREATED)) > '20071')
GROUP BY HD_QUEUE.ID desc, CONCAT(YEAR(T2.CREATED), MONTH(T2.CREATED)) desc

ORDER BY Queue_Name, Month
Answered 08/07/2010 by: RichB
Third Degree Green Belt

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