Help with JQL Query please

Question

Hello!

I'm responsible for quite a few boards at work on our JIRA and I have a query I need to refine a little bit, but I'm not sure how.  I'm hoping someone can help me.

Here is the current query:

Created >= 2020-01-01 AND created <= 2020-12-31 AND filter in ("Carli's Team - Assigned Tickets") AND status in (Submitted, Research) ORDER BY created ASC

I need to refine the Submitted status and the Research further:

* For Submitted, I need this to show anything that is older than 5 days from the date/time it was placed in Submitted status. 
* For Research status, I need this to show anything that is older than 14 days from the date/time the ticket was placed in Research status.

Is this possible?  If it is, how would I write the query?

Thank you in advance!  I do sincerely appreciate any assistance.

Michael Andolfatto_a62ntx · Answer

Out of the box I don't believe it's an option unfortunately. You would need a plugin for automation such as ScriptRunner or Automation for Jira to log the dates as well as some light administration to create the fields and apply the logic.

There is also the Time in Status plugin though I'm not sure it comes with custom JQL options to pull the data you need.

unknown · Answer

Thank you, Michael.  I tried that but I don't think they have ours set up so that we have those fields.   I've included snippets of how our tickets look, with the sensitive data blacked out.

Is there another way I can accomplish what I need?

Michael Andolfatto_a62ntx · Answer

Hi @[deleted],

To achieve this, you would need to have a date field generate when an issue transitions into Submitted and a second one when the issue transitions to Research.

By having Jira capture the date an issue arrives in each status you could include comparisons in your JQL to validate against such as

...AND (status = Submitted AND submittedDate >= -5d)