Tickets created by month for last 3 years
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
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
Answers (4)
Please log in to answer
Posted by:
RichB
14 years ago
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:
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
Posted by:
RichB
14 years ago
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
SELECT COUNT(*) AS '# of Tickets Created' FROM HD_TICKET T
WHERE DATEDIFF(NOW(), CREATED) < 1095
Posted by:
DContreras
14 years ago
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
Rating comments in this legacy AppDeploy message board thread won't reorder them,so that the conversation will remain readable.