JQL and start/end dates

dennish_dcsorg_com April 28, 2021

Is there a way to run a JQL statement that will display all issues in a project with 2 date fields that do not have the same date? We have a custom start and end date field and quite a few of them have the same date (for issues that haven't been dealt with yet). I'd like to run a search that doesn't include those issues.

 

When I try to create one and choose the field (Est Start Date) then choose != all I can choose are date options like current date, before today, etc. I'd like to be able to use something that lists issue where Est Start Date and Est End Date are not equal.

2 answers

0 votes
John Funk
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
April 28, 2021

Hi Dennish,

You can't do it with JQL - it does not have the functionality to compare dates. But you can do it by creating an Automation For Jira rule based on a scheduled trigger. 

Just write the JQL to get all of the issues you want to check, for instance Project = ABC

Then use the Advanced Compare Condition to compare the two date fields.

Finally, add a new Action to send an Email and include lookupIssues in the body. 

Here is some more information to get started with Automation For Jira if you are not familiar with it:

There are two types of automation:

  • Global: In the Free version, you get 100 executions per product, per month
  • Project: In all versions, you get unlimited executions per month

Automation Basics: https://www.atlassian.com/software/jira/guides/expand-jira/automation

You can do this via global rules to apply to all projects also. You'll need to be a Jira Admin/Site Admin, go to Jira Settings > System > Automation Rules (left-hand menu).

For more on using Automation For Jira see these help pages.

Jira Automation Template Library to help get you started quickly:

https://www.atlassian.com/software/jira/automation-template-library#/labels/all/customLabelId/1453

Bill Sheboy
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
April 28, 2021

Hi @John Funk 

For a scheduled trigger, I think that would end up sending an email for each issue found.  One challenge is the custom date fields aren't available with LookupIssues, so that cannot collapse to one email.  That is why I was thinking of the custom field to store the date difference and treat reporting/analysis as a separate issue.

Please help me understand if I am missing something as that would be a great solution otherwise.  Thanks!

Best regards,

Bill

John Funk
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
April 28, 2021

Hey Bill - but if you are just needing a list of issue that meet the Condition, you don't need to put the date in the lookupIssues section - just the key and maybe summary. 

Would that work?

Bill Sheboy
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
April 28, 2021

Hi, John.  I guess I'm not following what you suggested, so "maybe"  ;^)

I thought you were suggesting this:

  1. Trigger: scheduled on JQL
  2. Advanced compare condition: for the date check
  3. Action: lookup issues ???
  4. Action: send the email using lookup

I was noting that after the scheduled trigger, everything else in the rule gets run for each issue returned by the trigger's JQL. So, won't steps 2-4 repeat?

And, if the compare could be done with JQL alone (including the date check), that could be used stand-alone, or in the lookup issues with no scheduled trigger.  Right?

Thanks!

John Funk
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
April 28, 2021

You are the smart values king! So I would defer to what you think. And, yes, now that I look at it laid out that way, it probably does repeat 2-4 in that scenario. 

0 votes
Jack Brickey
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
April 28, 2021

Unfortunately that option does not exist without an addon. 

Bill Sheboy
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
April 28, 2021

Hi @dennish_dcsorg_com 

Yes, and to what Jack noted... How often do you need to perform this query/check?

  • one time: export to a spreadsheet and check with a formula
  • lots of times: consider purchasing a JQL add-on from the marketplace
  • somewhere between one and lots: add a custom field for the days difference between the dates, create an automation rule to set the value, and then query on the custom field.  Or if you don't want to add the new field and only expect a few "hits", write any discrepancies to the audit log.

Best regards,

Bill

Suggest an answer

Log in or Sign up to answer