/build/static/layout/Breadcrumb_cap_w.png

Scripting Question


K1000-Reports - How do I show the managers name in a report instead of the users ID.

07/27/2017 728 views
Hi Everyone,

I am hoping someone can help me with an SQL report I am trying to make to show new hires each week.
Here is the report sql right now.
SELECT HD_TICKET.TITLE,
HD_TICKET.CUSTOM_FIELD_VALUE4,
HD_TICKET.CUSTOM_FIELD_VALUE14,
HD_TICKET.CUSTOM_FIELD_VALUE0,
HD_TICKET.CUSTOM_FIELD_VALUE3,
HD_TICKET.CUSTOM_FIELD_VALUE7,
HD_TICKET.CUSTOM_FIELD_VALUE1,
HD_TICKET.CUSTOM_FIELD_VALUE2,
HD_TICKET.CUSTOM_FIELD_VALUE8 

FROM HD_TICKET 
LEFT JOIN USER_FIELD_VALUE O on (HD_TICKET.CUSTOM_FIELD_VALUE4 = O.USER_ID)

WHERE (HD_TICKET.HD_QUEUE_ID = 4) AND (((DATE(HD_TICKET.CUSTOM_FIELD_VALUE8) <= NOW() AND DATE(HD_TICKET.CUSTOM_FIELD_VALUE8) > DATE_SUB(NOW(),INTERVAL 7 DAY))) OR ((DATE(HD_TICKET.CUSTOM_FIELD_VALUE2) <= NOW() AND DATE(HD_TICKET.CUSTOM_FIELD_VALUE2) > DATE_SUB(NOW(),INTERVAL 7 DAY))) OR ((DATE(HD_TICKET.CUSTOM_FIELD_VALUE8) >= NOW() AND DATE(HD_TICKET.CUSTOM_FIELD_VALUE8) < DATE_ADD(NOW(),INTERVAL 7 DAY))) OR ((DATE(HD_TICKET.CUSTOM_FIELD_VALUE1) >= NOW() AND DATE(HD_TICKET.CUSTOM_FIELD_VALUE1) < DATE_ADD(NOW(),INTERVAL 7 DAY))) OR ((DATE(HD_TICKET.CUSTOM_FIELD_VALUE2) >= NOW() AND DATE(HD_TICKET.CUSTOM_FIELD_VALUE2) < DATE_ADD(NOW(),INTERVAL 7 DAY))))  ORDER BY TITLE, CUSTOM_FIELD_VALUE4

I know i need to add something like LEFT JOIN USER O ON HD_TICKET.ID = O.ID to the sql code, but I can't seem to figure out how to link the managers name in the User table to HD_TICKET.CUSTOM_FIELD_VALUE4.

Any help with this is greatly appreciated!!!
Amanda
0 Comments   [ + ] Show comments

Comments


All Answers

0
For anyone experiencing this, I've found the solution:
Here is the SQL code.

SELECT HD_TICKET.CUSTOM_FIELD_VALUE10 as Employee_Name,

HD_TICKET.CUSTOM_FIELD_VALUE4 as EMPLOYEE_TYPE,

HD_TICKET.CUSTOM_FIELD_VALUE14 as LOCATION,

HD_TICKET.CUSTOM_FIELD_VALUE0 as Department,

O.FULL_NAME as MANAGER,

HD_TICKET.CUSTOM_FIELD_VALUE7 as PHONE_EXTENSION,

HD_TICKET.CUSTOM_FIELD_VALUE1 as START_DATE,

HD_TICKET.CUSTOM_FIELD_VALUE2 as END_DATE,

HD_TICKET.CUSTOM_FIELD_VALUE8 as CHANGE_DATE 

 

FROM HD_TICKET 

LEFT JOIN USER O on (HD_TICKET.CUSTOM_FIELD_VALUE3 = O.ID)

WHERE (HD_TICKET.HD_QUEUE_ID = 4) AND(((DATE(HD_TICKET.CUSTOM_FIELD_VALUE8) <= NOW() ANDDATE(HD_TICKET.CUSTOM_FIELD_VALUE8) > DATE_SUB(NOW(),INTERVAL 7 DAY))) OR((DATE(HD_TICKET.CUSTOM_FIELD_VALUE2) <= NOW() ANDDATE(HD_TICKET.CUSTOM_FIELD_VALUE2) > DATE_SUB(NOW(),INTERVAL 7 DAY))) OR((DATE(HD_TICKET.CUSTOM_FIELD_VALUE8) >= NOW() ANDDATE(HD_TICKET.CUSTOM_FIELD_VALUE8) < DATE_ADD(NOW(),INTERVAL 7 DAY))) OR((DATE(HD_TICKET.CUSTOM_FIELD_VALUE1) >= NOW() AND DATE(HD_TICKET.CUSTOM_FIELD_VALUE1)< DATE_ADD(NOW(),INTERVAL 7 DAY))) OR ((DATE(HD_TICKET.CUSTOM_FIELD_VALUE2)>= NOW() AND DATE(HD_TICKET.CUSTOM_FIELD_VALUE2) <DATE_ADD(NOW(),INTERVAL 7 DAY))))  ORDERBY TITLE, CUSTOM_FIELD_VALUE4

Answered 07/28/2017 by: aniles
White 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