/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

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:
Posted by: chucksteel 6 years ago
Red Belt
0

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

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