Unassigned notification after 2 hours, but only during work hours/days.
I'm trying to make a cutom rule (I am not a sql guru) that notifies our department if a ticket has sat for more than two hours. I've got that piece working, but i only want it to notify us during work hours and days.
Anyone have some insight on why the business hours piece isn't working?
Here is my rule;
Select T.ID as 'Ticket ID', T.TITLE as Issue, S.FULL_NAME as Submitter, T.CREATED as Created FROM HD_TICKET T
JOIN USER S ON (S.ID = T.SUBMITTER_ID)
WHERE T.CREATED < SUBDATE(NOW(), INTERVAL 2 HOUR)
AND (HOUR(NOW()) > 06 OR HOUR(NOW()) < 18) AND HD_QUEUE_ID = 5
AND DAYOFWEEK(NOW()) not in (1,7)
AND T.OWNER_ID = 0
AND HD_STATUS_ID = 33
ORDER BY T.ID ASC
1 Comment
[ + ] Show comment
Answers (0)
Please log in to answer
Be the first to answer this question
(HOUR(NOW()) BETWEEN 05 and 19) instead of
(HOUR(NOW()) > 06 OR HOUR(NOW()) < 18)
If you use the view ticket search results link do you get any results?
What is your schedule for running the rule? - chucksteel 6 years ago
This is scheduled to run every hour.
I get the following result when I run the rule with the above changes.
10/09/2017 15:20:58> Starting: 10/09/2017 15:20:58 10/09/2017 15:20:58> Executing Select Query... 10/09/2017 15:20:58> mysqli error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(HOUR(NOW()) BETWEEN 05 and 19) AND HD_QUEUE_ID = 5 AND DAYOFWEEK(NOW()) not in ' at line 4] in EXECUTE("Select T.ID as 'Ticket ID', T.TITLE as Issue, S.FULL_NAME as Submitter, T.CREATED as Created FROM HD_TICKET T JOIN USER S ON (S.ID = T.SUBMITTER_ID) WHERE T.CREATED < SUBDATE(NOW(), INTERVAL 2 HOUR) (HOUR(NOW()) BETWEEN 05 and 19) AND HD_QUEUE_ID = 5 AND DAYOFWEEK(NOW()) not in (1,7) AND T.OWNER_ID = 0 AND HD_STATUS_ID = 33 ORDER BY T.ID ASC") - jaredkent 6 years ago
SUBDATE(NOW(), INTERVAL 2 HOUR) (HOUR(NOW()...
Should be
SUBDATE(NOW(), INTERVAL 2 HOUR) AND (HOUR(NOW()... - chucksteel 6 years ago