/bundles/itninjaweb/img/Breadcrumb_cap_w.png
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