/build/static/layout/Breadcrumb_cap_w.png

Scripting Question


SQL Statement Help

03/21/2018 628 views
I am building scheduled tickets using a second queue that opens the ticket on our primary queue.  (I found the process here)  But we all know how scheduling works using custom ticket rules.  I am wondering if there is way to write a SQL statement that will create the ticket quarterly, yearly, every so many days, and last business day of of the month.  I have little knowledge of SQL so any help would be appreciative!

Thanks!
0 Comments   [ + ] Show comments

Comments


All Answers

0
You will need to look into the MySQL datetime functions:

Using those functions, and some help from Google/StackOverflow you can figure out if today is the first day of the quarter. That allows you to add something like:
DATE(NOW()) = (
SELECT  MAKEDATE(YEAR(CURDATE()), 1) + INTERVAL QUARTER(CURDATE()) QUARTER 
                                       - INTERVAL    1 QUARTER )
To your where clause and the ticket rule will only return results when that statement is true.

Reference: https://stackoverflow.com/questions/11884618/how-do-i-get-the-first-date-of-a-quarter-in-mysql

Answered 03/22/2018 by: chucksteel
Red Belt

  • This is great! I'm still stumped on the last business day of the month that is not a weekend day.
    • Did you ever figure out how to create a ticket rule to fire off the last business day of every month? Kace application really lacks big time in recurring ticket functionality.
      • I came up with a test query that should run today. If it doesn't run tomorrow, I'll post it.
      • See the answer that I submitted here:
        https://www.itninja.com/question/ticket-rule-last-business-day-of-every-month

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