/build/static/layout/Breadcrumb_cap_w.png

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
Daydate(HD TICKET.created)Tickets Opened
Tuesday11/01/2016 00:00:0015
Wednesday11/02/2016 00:00:0022
Thursday11/03/2016 00:00:0014
Friday11/04/2016 00:00:0011
Monday11/07/2016 00:00:0013




0 Comments   [ + ] Show comments

Answers (1)

Answer Summary:
Posted by: chucksteel 7 years ago
Red Belt
1

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

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ