K1000 - Service Desk - SQL Query assistance
Hello, looking to see if someone can assist with the query below. Trying to send a reminder email after a certain number of days if survey is not filled.
Select SQL:
SELECT DISTINCT HD_TICKET.ID
,HD_TICKET.ID AS TICKET_ID # $TICKET_ID
,HD_TICKET.TITLE AS TICKET_TITLE # $TICKET_TITLE
,HD_STATUS.NAME AS STATUS_NAME
,HD_STATUS.STATE AS STATE
,SUBMITTER.FULL_NAME AS SUBMITTER_FULLNAME
,SUBMITTER.EMAIL AS SUBMITTER_EMAIL
,HD_TICKET.TIME_CLOSED
,HD_TICKET.SATISFACTION_RATING AS RATING
,HD_TICKET.SATISFACTION_COMMENT AS COMMENT
,HD_TICKET.ID AS TICKET_ID # $TICKET_ID
,HD_TICKET.TITLE AS TICKET_TITLE # $TICKET_TITLE
,HD_STATUS.NAME AS STATUS_NAME
,HD_STATUS.STATE AS STATE
,SUBMITTER.FULL_NAME AS SUBMITTER_FULLNAME
,SUBMITTER.EMAIL AS SUBMITTER_EMAIL
,HD_TICKET.TIME_CLOSED
,HD_TICKET.SATISFACTION_RATING AS RATING
,HD_TICKET.SATISFACTION_COMMENT AS COMMENT
FROM (HD_TICKET, HD_STATUS)
LEFT JOIN USER SUBMITTER ON SUBMITTER.ID = HD_TICKET.SUBMITTER_ID
LEFT JOIN USER SUBMITTER ON SUBMITTER.ID = HD_TICKET.SUBMITTER_ID
WHERE HD_STATUS.ID = HD_TICKET.HD_STATUS_ID
AND HD_STATUS.STATE = 'closed' -- ticket is still closed
AND -- no survey rating or comment
(SATISFACTION_COMMENT IS NULL
OR SATISFACTION_COMMENT LIKE ''
OR SATISFACTION_RATING IS NULL
OR SATISFACTION_RATING LIKE ''
)
AND HD_TICKET.HD_QUEUE_ID = 20 -- insert your queue id here
AND DATE(TIME_CLOSED) = DATE(NOW())-1 -- closed 90 days ago
When I call the variables $TICKET_ID and $TICKET_TITLE under "Email each recipient in query results" the email gets sent out correctly but the variables do not give the values and only show up as variables in the email that is sent out
1 Comment
[ + ] Show comment
-
Thanks Hobbsy that worked - kace_admin 6 years ago
Answers (1)
Answer Summary:
Please log in to answer
Posted by:
Hobbsy
6 years ago
Top Answer
Make sure in your email template you have the variables in lower case
