JQL and start/end dates

[Dennis_H]

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.

[John Funk]

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]

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]

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]

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]

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. 

[Jack Brickey]

Unfortunately that option does not exist without an addon. 

[Bill Sheboy]

Hi @Dennis_H 

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