/build/static/layout/Breadcrumb_cap_w.png

KACE Product Support Question


Report on tickets missing work

07/03/2015 921 views
I'm trying to come up with a quick report that can tell me when a support ticket is closed, but has no work attached to it.  I've started with a simple query that returns all tickets (open or closed), but I'm getting the reverse of what I'm looking for.

The following query will return all tickets WITH work, instead of all tickets WITHOUT work.  I'm pretty much trying to say "If the Ticket ID from HD_TICKET doesn't show up in HD_WORK, return that information."

SELECT T.ID
FROM HD_TICKET T
LEFT JOIN
HD_WORK W
ON W.HD_TICKET_ID = T.ID
WHERE W.HD_TICKET_ID IS NOT NULL

I know I'm probably missing something simple, but I just can't get it sorted.  Thanks in advance if anyone has any input.

EDIT:
Got it figured out!

SELECT T.ID AS TICKET, T.CREATED as CREATED,
ifnull((select FULL_NAME from USER where T.OWNER_ID = USER.ID),' Unassigned') as OWNER_NAME
FROM HD_TICKET T
LEFT JOIN HD_WORK W ON W.HD_TICKET_ID = T.ID
LEFT JOIN HD_STATUS on HD_STATUS_ID = HD_STATUS.ID
WHERE NOT EXISTS (SELECT 1 FROM HD_WORK W WHERE W.HD_TICKET_ID = T.ID)
AND HD_STATUS.STATE = 'closed'
ORDER BY T.ID
0 Comments   [ + ] Show comments

Comments


Be the first to answer this question

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