Service Desk SQL question
Hi all, I'm trying to add an additional field to the following SQL query to add custom5 in the results. This is a pull down for the number of devices that the request is for. Thanks in advance!
SELECT IFNULL(O.FULL_NAME,'Unassigned') AS 'Owner', COUNT(T.ID) AS '# of Tickets' FROM HD_TICKET T
JOIN HD_STATUS S ON (T.HD_STATUS_ID = S.ID)
LEFT JOIN USER O ON (T.OWNER_ID = O.ID)
WHERE (S.STATE = 'closed' AND DATEDIFF(NOW(), T.TIME_CLOSED) < 365)
GROUP BY O.FULL_NAME
order by COUNT(T.ID) DESC
Answers (1)
Custom Value 5 is stored in the database as CUSTOM_FIELD_VALUE4 so you could change the SELECT portion of the query to be:
SELECT IFNULL(O.FULL_NAME,'Unassigned') AS 'Owner', COUNT(T.ID) AS '# of Tickets', CUSTOM_FIELD_VALUE4 as '# of Devices'
I'm not sure if this will produce the results that you're looking for, however. Your current query will produce a list of owners and how many tickets they have, but adding a specific field from individual tickets will change that result. What exactly are you trying to report on? Do you want the sum of values reported in the custom field?
Comments:
-
Thank you for getting back to me.
Yes, I want to be able to sum up the value choosen in the # of devices field. This will allow me to run a quick report that will show me the number of tickets a technician complete, as well as the number of devices they touched. Many times people in computer labs and libraries will put in a single ticket for 30 or so machines... If I just report on the number of tickets the guys complete it really won't reflect how much work they really did... - ddevore 12 years ago -
In that case using SUM(CUSTOM_FIELD_VALUE4) should work. - chucksteel 12 years ago
-
Thanks again! I added this line and the report runs, but the sum is 0 for everyone now... Could it be that since the CUSTOM_5 field is a sigle select field it's not adding them up? Here is what I have right now:
SELECT IFNULL(O.FULL_NAME,'Unassigned') AS 'Owner', COUNT(T.ID) AS '# of Tickets', SUM('CUSTOM_5') AS '# of Devices' FROM HD_TICKET T
JOIN HD_STATUS S ON (T.HD_STATUS_ID = S.ID)
LEFT JOIN USER O ON (T.OWNER_ID = O.ID)
WHERE (S.STATE = 'closed' AND DATEDIFF(NOW(), T.TIME_CLOSED) < 5)
GROUP BY O.FULL_NAME - ddevore 12 years ago -
I downloaded the MySQL work bench and believe I had the table name wrong, I set it to the following but still have no results.
SELECT IFNULL(O.FULL_NAME,'Unassigned') AS 'Owner', COUNT(T.ID) AS '# of Tickets', SUM('CUSTOM_FIELD_VALUES5') AS '# of Devices' FROM HD_TICKET T
JOIN HD_STATUS S ON (T.HD_STATUS_ID = S.ID)
LEFT JOIN USER O ON (T.OWNER_ID = O.ID)
WHERE (S.STATE = 'closed' AND DATEDIFF(NOW(), T.TIME_CLOSED) < 5)
GROUP BY O.FULL_NAME - ddevore 12 years ago -
If your field is custom value 5 then the column in the table is CUSTOM_FIELD_VALUE4. The columns in the database are 0 based so you always have to subtract one from the number in the configuration for the table's column. - chucksteel 12 years ago
