Attempting to create KACE hours report for tickets that have a custom field set to a specific value
Attempting to create KACE hours report for tickets that have a custom field set to a specific value.
We have set Custom Field Value 2 as a dropdown menu that technicians select when they are doing an afterhours ticket.
I am trying to pull a report for tickets that have this field set and list the hours worked on these tickets.
The below query brings back more hours than the base hours worked report brings back.
Can anyone tell me what Im doing wrong here?
SELECT
USER.USER_NAME as 'Technician'
,T.ID as 'Ticket'
, format(SUM(time_to_sec(timediff(W.stop, W.start)))/3600.0,2) as 'Hours Worked'
FROM (HD_WORK W, HD_TICKET T)
JOIN USER on W.USER_ID = USER.ID
WHERE W.STOP > DATE_SUB(NOW(), INTERVAL 31 DAY)
and T.CUSTOM_FIELD_VALUE1 = 'After Hours'
GROUP BY USER.USER_NAME
0 Comments
[ + ] Show comments
Answers (1)
Answer Summary:
Please log in to answer
Posted by:
chucksteel
6 years ago
Top Answer
You don't have a relationship defined between the HD_WORK table and the HD_TICKET table, so it is probably pulling in tickets that should not be included. I generally prefer to use JOIN statements instead of selecting from multiple tables to make sure my relationships are clearly defined.
Try removing:
FROM (HD_WORK W, HD_TICKET T)
and replacing with:
FROM HD_WORK W
JOIN HD_TICKET T on T.ID = W.HD_TICKET_ID
Also, since you are grouping by USER.USER_NAME including T.ID in the fields will be problematic. If you want a breakdown of hours worked per ticket and per technician use:
GROUP BY USER.USERNAME, T.ID
