Need some Advanced JQL for not counting time over a weekend?  Keep reading…

A client recently asked for some advanced JQL magic.  I’ll admit my first thought was something like, “Jira can’t do that”, but in a flash, my next thought was, “Oh, wait – Automation for Jira can probably handle this!”**

Scenario:

I had already set up an approval workflow with three levels of approvals.  Additionally, Automation for Jira had been used to configure the triggers, logic, and actions to easily handle the Approval scenarios. (More in an upcoming article.)

All you need to know about the previous setup is this:

The workflow was configured such that if the first-level approver hadn’t approved/rejected the issue within one day, the issue’s 2nd level approver would be inserted automatically into the same approver field and an email with all the issue details would be sent to the new Approver.

Here’s the new requirement:

  1. Instead of one day to approve, the first approver should have 3 days to approve, AND
  2. If any of those three days were over a weekend, they shouldn’t be counted. (Only count the last 3businessdays.)

Jira can handle #1, but not #2.

Let’s recap the logic:

If the status is “Waiting for approval” and the status hadn’t changed within the last three business days, add the 2nd level approver to the Approvers field and kick off the email with issue details

(The two login areas in bold, above, were the parts that could not be done in Jira/Jira Service Desk and required an add-on (app).)

Using Automation for Jira, this is possible!

Here’s the secret sauce:

Modify and use the following JQL (with Automation for Jira Smart Values) in a Schedule Trigger, like this:

status = “YOUR-STATUS-HERE” AND not status changed AFTER “{{now.minusBusinessDays(3).asJqlDateTime}}”

Then, just set up your other actions as necessary, test, and enjoy the satisfaction from your new ninja JQL skills in Automation for Jira.

(NOTE: This “JQL” will not work in other JQL filters in Jira.  It only works in Automation for Jira.)

**Many thanks to Nick and John, over at Automation for Jira for coming up with the solution, and to the whole team at Automation for Jira for making these advanced tricks possible!