/build/static/layout/Breadcrumb_cap_w.png

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
 
FROM (HD_TICKET, HD_STATUS)
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 5 years ago

Answers (1)

Answer Summary:
Posted by: Hobbsy 5 years ago
Red Belt
1

Top Answer

Make sure in your email template you have the variables in lower case

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