/build/static/layout/Breadcrumb_cap_w.png

KACE Product Support Question


Need help with SQL for reporting on total tickets closed by owner per month

06/24/2015 2067 views
I'm still brand new at SQL and learning the Kbox schema. It's not clear if HD_Ticket.Owner_ID can be cross referenced to User.User ID and if so, then I'm not sure how to display the Full_Name of the user with ownership. I keep getting an 'unknown column User.Full_Name in field list' in my Select statement.  This is what I have so far:

Select count(*) as TotalTicketCount, Monthname(TIME_CLOSED) as Month, User.FULL_NAME as Owner
  From HD_TICKET
 
  Left Join USER as U
    on HD_Ticket.OWNER_ID=U.USER_ID
  where HD_QUEUE_ID = 'Help Desk'
        and Year(TIME_CLOSED) = '2014'
        and Owner in ('name1', 'name2', etc)
  Group by Owner, Monthname(TIME_CLOSED);

Is their a better way to request a list of owners?

Thanks for any and all help.
0 Comments   [ + ] Show comments

Comments


All Answers

0
You can actually create a report using the wizard that will give you SQL as below

SELECT O.USER_NAME AS OWNER_USER_NAME,
HD_TICKET.ID,
HD_TICKET.TIME_CLOSED,
HD_TICKET.TITLE 
FROM HD_TICKET  LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID) JOIN HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID) 
WHERE (HD_TICKET.HD_QUEUE_ID = 3) AND ((HD_STATUS.NAME = 'Closed'))  ORDER BY OWNER_USER_NAME
You can see that the Owner_ID shown in the ticket does match the USER>ID value and as such to get the name you need to join the tables on the ID field 
Answered 06/25/2015 by: Hobbsy
Red Belt

0
You are getting the error because when you created the join to the user table you aliased it as U. Therefore, when you want to select columns from the table you need to use U.FULL_NAME and not USER.FULL_NAME. I would suggest that when you are building your joins you should use a descriptive alias:
JOIN USER OWNER on OWNER.ID = HD_TICKET.OWNER_ID

If you do this then your select statement looks like OWNER.FULL_NAME which make it clear what you are selecting. If you build more complicated reports that include multiple users, i.e. owner, submitter and approver, this will help keep things clean.

Answered 06/26/2015 by: chucksteel
Red Belt

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