Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in
Deleted user
0 / 0 points
Next:
badges earned

Your Points Tracker
Challenges
Leaderboard
  • Global
  • Feed

Badge for your thoughts?

You're enrolled in our new beta rewards program. Join our group to get the inside scoop and share your feedback.

Join group
Recognition
Give the gift of kudos
You have 0 kudos available to give
Who do you want to recognize?
Why do you want to recognize them?
Kudos
Great job appreciating your peers!
Check back soon to give more kudos.

Past Kudos Given
No kudos given
You haven't given any kudos yet. Share the love above and you'll see it here.

It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

JQL and start/end dates

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
Jack Community Leader Apr 28, 2021

Unfortunately that option does not exist without an addon. 

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

0 votes
John Funk Community Leader Apr 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

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 Apr 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?

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 Apr 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. 

Suggest an answer

Log in or Sign up to answer
TAGS
Community showcase
Published in Marketplace Apps & Integrations

Why everyone using Jira must be GDPR-compliant

Did you know that penalties up to 4 % of the yearly company turnover are possible in case of GDPR violations? GDPR regulations are currently mainly relevant for companies in the EU, but countries lik...

98 views 1 3
Read article

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you