K1000-Reports - How do I show the managers name in a report instead of the users ID.
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
Answers (1)
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