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

Comments

  • Try using
    (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?
    • Thanks for the comment.

      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")
      • You dropped an AND.
        SUBDATE(NOW(), INTERVAL 2 HOUR) (HOUR(NOW()...

        Should be
        SUBDATE(NOW(), INTERVAL 2 HOUR) AND (HOUR(NOW()...
  • This content is currently hidden from public view.
    Reason: Removed by member request
    For more information, visit our FAQ's.
Please log in to comment

There are no answers at this time

Answers

Answer this question or Comment on this question for clarity