JQL: How to get status in certain status for more than x weeks.

[Jairo Cabello]

Hello!

In our team, we are trying to figure out how many tickets have been in status "In Progress" for more than 3 weeks, but that are not necessarily "in progress" anymore.

If my ideal JQL would exist it would be like this:
"Status was "In Progress" for 3w"

This would return me any issue no matter which status it is now that at some point has been "In Progress" for over 3 weeks.

Is there any simple way to get this? Or maybe not a simple one? I've considered writing a Scriptrunner custom JQL function.

Regards and thanks!

[Ravi Sagar _Sparxsys_ · Accepted solution]

Hi @Jairo Cabello 

Interesting question.

A JQL something like this will return all the issues that were in "In Progress" 3 weeks ago, irrespective of their current status.

status was "In Progress" and status changed after -3w

You wan't to calculate the Issues that were "In Progress" for more 3 weeks at any given point in the past. I believe this can be done using ScriptRunner.

Refer to this page as a starting point: https://scriptrunner.adaptavist.com/latest/jira/recipes/scriptfields/dateOfFirstTransition.html

The code on this link is fetching the created date but "getChangeItemsForField" gets all the transitions you are doing on an Issue with multiple entries for each transition. Something like this.

If you look at the output above, there is an entry for "In Progress" with "created=2019-03-21". This is the date when the issue was transitioned to "In Progress". The next item also has a similar date. Calculating the difference will tell you how long that issue was in a specific status.

I hope it helps. Sorry I don't have a complete code but I tried giving you some pointers (if you want to write a script).

Ravi