KACE Product Support

The HD_TICKET table doesn't contain a column for SUBMITTER_EMAIL. You want to select the EMAIL column from the USER table. In this particular query, the USER table is being joined with this statement:

left join USER on HD_TICKET.SUBMITTER_ID = USER.ID

This means that you can select columns referencing the submitter like this:

USER.EMAIL

Personally, I prefer to join to the user table and use an alias to make it more meaningful, like this:

left join USER SUBMITTER on SUBMITTER.ID = HD_TICKET.SUBMITTER_ID

This makes the select clause of the query more clear, because the columns are then selected like this:

SUBMITTER.EMAIL

I generally avoid sub-select statements, where possible and prefer using a join. Again, I find it to be clearer. In the case of your query, that means I would eliminate these:

(select LDAP_UID from USER where HD_TICKET.SUBMITTER_ID = USER.ID) as SUBMITTER_NAME,
(Select LDAP_UID from USER where HD_TICKET.OWNER_ID = USER.ID) as OWNER_NAME,

We already have a join to the USER table for the submitter, so there's no need for the sub-select statement anyway. Let's add a join to the USER table referencing the owner.

left join USER OWNER on OWNER.ID = HD_TICKET.OWNER_ID

The final result is this query:

 SUBMITTER.LOCATION_ID,
 SUBMITTER.LDAP_UID as SUBMITTER_NAME,
OWNER.LDAP_UID as OWNER_NAME,
 ABS(DATEDIFF(HD_TICKET.TIME_CLOSED, HD_TICKET.CREATED)) + 1
     - ABS(DATEDIFF(ADDDATE(HD_TICKET.TIME_CLOSED, INTERVAL 1 - DAYOFWEEK(HD_TICKET.TIME_CLOSED)DAY),
                    ADDDATE(HD_TICKET.CREATED, INTERVAL 1 - DAYOFWEEK(HD_TICKET.CREATED) DAY))) / 7 * 2
     - (DAYOFWEEK(IF(HD_TICKET.CREATED < HD_TICKET.TIME_CLOSED, HD_TICKET.CREATED, HD_TICKET.TIME_CLOSED)) = 1)
     - (DAYOFWEEK(IF(HD_TICKET.CREATED > HD_TICKET.TIME_CLOSED, HD_TICKET.CREATED, HD_TICKET.TIME_CLOSED)) = 7) - 1 AS DAYS_OPEN_NO_WKND
from HD_TICKET
left join HD_CATEGORY on HD_CATEGORY_ID = HD_CATEGORY.ID
JOIN HD_QUEUE Q ON Q.ID = HD_TICKET.HD_QUEUE_ID
left join HD_STATUS on HD_STATUS_ID = HD_STATUS.ID
left join HD_PRIORITY on HD_PRIORITY_ID = HD_PRIORITY.ID
left join USER SUBMITTER on SUBMITTER.ID = HD_TICKET.SUBMITTER_ID
WHERE MONTH(HD_TICKET.CREATED) > 5 and YEAR(HD_TICKET.CREATED) = 2020
order by OWNER_NAME