Generate report of tickets per day per location
We are looking to generate a report where we can get an idea of the volume of tickets per location (building) broken down by day. The location field is being populated via AD in the location field. See below for sample output:
Day Date. User Location. # Tickets.
Monday 1/1/2016. XXXXXX 3
Monday 1/1/2016. YYYYYY 7
Monday 1/1/2016. ZZZZZZZ 8
Tuesday 1/2/2016. XXXXXX 5
Tuesday 1/2/2016. YYYYYY 13
Tuesday 1/2/2016. ZZZZZZZ 2
We have something similar but did not see the location field per user in the database scheme
SELECT DAYNAME(HD_TICKET.CREATED) AS `DAY`, date(HD_TICKET.created),
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 = 1)
AND MONTH(HD_TICKET.CREATED) = MONTH(DATE_SUB(NOW(),INTERVAL 1 Week))
AND YEAR(HD_TICKET.CREATED) = YEAR(DATE_SUB(NOW(),INTERVAL Week))
GROUP BY DAY(HD_TICKET.CREATED)
Sample output tweaked for post
| Day | date(HD TICKET.created) | Tickets Opened |
|---|---|---|
| Tuesday | 11/01/2016 00:00:00 | 15 |
| Wednesday | 11/02/2016 00:00:00 | 22 |
| Thursday | 11/03/2016 00:00:00 | 14 |
| Friday | 11/04/2016 00:00:00 | 11 |
| Monday | 11/07/2016 00:00:00 | 13 |
0 Comments
[ + ] Show comments
Answers (1)
Answer Summary:
Please log in to answer
Posted by:
chucksteel
7 years ago
Top Answer
You need to join to the USER table on the submitter ID (assuming you are referring to the submitter's location) and then select the LOCATION column. Our locations are in the format of "building, room" so I'm just selecting and grouping by the first part of the field in the query below.
SELECT DAYNAME(HD_TICKET.CREATED) AS `DAY`, date(HD_TICKET.created), SUBSTRING_INDEX(S.LOCATION, ",", 1) as "Building",
COUNT(HD_TICKET.ID) as 'Tickets Opened'
FROM HD_TICKET
LEFT JOIN USER S ON (S.ID = HD_TICKET.SUBMITTER_ID)
WHERE (HD_TICKET.HD_QUEUE_ID = 1)
AND MONTH(HD_TICKET.CREATED) = MONTH(DATE_SUB(NOW(),INTERVAL 1 Week))
AND YEAR(HD_TICKET.CREATED) = YEAR(DATE_SUB(NOW(),INTERVAL 1 Week))
GROUP BY DAY(HD_TICKET.CREATED), SUBSTRING_INDEX(S.LOCATION, ",", 1)
Also, I removed the superfluous joins to the category table and the user table based on ticket owner.
Comments:
-
Thank you sir. This is perfect! - solarissparc 7 years ago
