K1000 Service Desk Report: # of tickets created per week
I want to create a report for the # of tickets created each week for a date range. I have a query to get a count for one date range, but I'm wondering if I could have a date range broken down by week. So, the date range might be a month and I want it to give me the # for each week in that date range. This is a bit beyond my SQL skill level. Here's my query for simple count for a date range:
select COUNT(HD_TICKET.CREATED)
from HD_TICKET
Where HD_TICKET.CREATED between '2013-07-28' AND '2013-08-03'
0 Comments
[ + ] Show comments
Answers (1)
Answer Summary:
Please log in to answer
Posted by:
chucksteel
11 years ago
This shows the week of the year and the number of tickets:
select week(HD_TICKET.CREATED), COUNT(HD_TICKET.CREATED) from HD_TICKET Where HD_TICKET.CREATED between '2013-06-28' AND '2013-08-03' GROUP BY week(HD_TICKET.CREATED)
This is a little nicer format:
select DATE(DATE_ADD(MAKEDATE(YEAR(CREATED), 1), INTERVAL WEEK(CREATED) WEEK) -2) as 'Week beginning', COUNT(HD_TICKET.CREATED) from HD_TICKET Where HD_TICKET.CREATED between '2013-07-07' AND '2013-08-03' GROUP BY week(HD_TICKET.CREATED)
Comments:
-
Awesome! That second one is purty! Thanks a lot! - lmland 11 years ago
-
I like the report and was wondering what modifications would I need to make something similar, but breaking down based on day for a month.? - solarissparc 8 years ago
-
So you want a report of the number of tickets opened per day in a particular month? - chucksteel 8 years ago
-
yes sir. - solarissparc 8 years ago
-
Here is the count of tickets opened per day in the previous month:
SELECT DATE(HD_TICKET.CREATED) as "Day",
COUNT(HD_TICKET.ID) as "Tickets Opened"
FROM HD_TICKET
JOIN HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID)
LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID)
WHERE (HD_TICKET.HD_QUEUE_ID = 2)
AND MONTH(HD_TICKET.CREATED) = MONTH(DATE_SUB(NOW(),INTERVAL 1 MONTH))
AND YEAR(HD_TICKET.CREATED) = YEAR(DATE_SUB(NOW(),INTERVAL 1 MONTH))
GROUP BY DAY(HD_TICKET.CREATED)
For a specific month:
SELECT DATE(HD_TICKET.CREATED) as "Day",
COUNT(HD_TICKET.ID) as "Tickets Opened"
FROM HD_TICKET
JOIN HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID)
LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID)
WHERE (HD_TICKET.HD_QUEUE_ID = 2)
AND MONTH(HD_TICKET.CREATED) = 11
AND YEAR(HD_TICKET.CREATED) = 2016
GROUP BY DAY(HD_TICKET.CREATED) - chucksteel 8 years ago-
Thank you for your help. This is exactly what we are looking to accomplish. - solarissparc 8 years ago
