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

Please log in to comment

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

Please log in to comment
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

Please log in to comment
Answer this question or Comment on this question for clarity