/build/static/layout/Breadcrumb_cap_w.png

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
  • 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? - chucksteel 3 years ago
    • 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") - jaredkent 3 years ago
      • You dropped an AND.
        SUBDATE(NOW(), INTERVAL 2 HOUR) (HOUR(NOW()...

        Should be
        SUBDATE(NOW(), INTERVAL 2 HOUR) AND (HOUR(NOW()... - chucksteel 3 years ago

Answers (0)

Be the first to answer this question

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