How would we limit a K1000 helpdesk ticket email rule to a single queue?
We use a ticket rule to send hourly emails listing all new helpdesk tickets. This week we added a second queue to our K1000 for another department, however the ticket rule is now being triggered by tickets in both queues. We've tried adding 'AND HD_TICKET.HD_QUEUE_ID = 1' but it caused an error. Any suggestions would be warmly welcomed :)
CURRENT RULE:
SELECT
T.ID AS TICKNUM,
T.TITLE AS TITLE,
S.NAME AS STATUS,
U.EMAIL AS SUBMITTER_EMAIL,
U.FULL_NAME AS SUBMITTER_FULLNAME,
CONCAT ('http://kbox1.OurCompany.org/adminui/ticket.php?ID=' , T.ID) AS TICKET_LINK
FROM HD_TICKET T JOIN HD_STATUS S ON (T.HD_STATUS_ID = S.ID) JOIN USER U ON (T.SUBMITTER_ID = U.ID) WHERE HOUR(NOW()) > 6 AND HOUR(NOW()) < 19 AND NOW() > DATE_ADD(T.CREATED, INTERVAL 15 MINUTE) AND S.NAME = 'New'
/******* begin only during business hours ***********/
/**/and HOUR(NOW())>=7 /* 7am or later kbox time*/
/**/and CURTIME()<='18:00:00' /* before 6pm Pacific */
/**/and DAYNAME(NOW()) NOT IN ('Saturday','Sunday') /* not the weekend */
/**/and DAYOFYEAR(NOW()) NOT IN (1,360) /* not xmas or new year's */
/**/and NOT( /*not U.S. thxgiving 2 days */
/**/ MONTHNAME(CURDATE())='November' and (
/**/ (DAYNAME(CURDATE())='Thursday' and DAYOFMONTH(CURDATE()) IN (22,23,24,25,26,27,28)) OR
/**/ (DAYNAME(CURDATE())='Friday' and DAYOFMONTH(CURDATE()) IN (23,24,25,26,27,28,29))
/**/) ) and NOT( /* not U.S. Labor Day */
/**/ MONTHNAME(CURDATE())='September' and DAYNAME(CURDATE())='Monday' and DAYOFMONTH(CURDATE()) IN (1,2,3,4,5,6,7) )
/******* end only during business hours ***********/
-
Have you tried using 'AND T.HD_QUEUE_ID = 1' since you aliased HD_TICKET as T? - grayematter 10 years ago
-
Worked like a charm. Thank you so much for the assist! - jvincent 10 years ago