/build/static/layout/Breadcrumb_cap_w.png
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
Answer Summary:
0 Comments   [ + ] Show comments

Comments


Answer Chosen by the Author

0
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

Answered 05/07/2018 by: chucksteel
Red Belt