/build/static/layout/Breadcrumb_cap_w.png
07/27/2017 629 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