Stories in a given status more than x days sorted by duration

Question

I would like to find issues that have not changed the status in a given period but sorting the result by the duration on a given status. For example, let's say I would like to know stories in BUILD COMPLETE status more than 10 days for example:

project = XXXX AND issuetype = Story AND status = "BUILD COMPLETE" 
    AND status changed BEFORE -10d

but getting the result sorted from older to newer stories on a given status. For example if I have an issue that is on BUILD COMPLETE more than 20 days will show first than a story that is on this status more than 10 days.

Is there a field that shows the number of days on  a given status, so I can see it in the result of the query?

I appreciate any solution that does not require a plug-in.

David Leal_28ge8x · Answer

@Wade Tracy _Boise_ ID_ thanks for your answer, it looks like there is no easy solution for a simple query. I would like to provide to my team the possibility to identify issues with no progress from JQL, but it seems there is no solution for that. The REST API is a good option, but it requires to much effort for parsing the JSON object and then putting the information into an excel table, for just having the information sorted. It is a shame that Jira does not offer this out of the box.

Wade Tracy _Boise_ ID_ · Answer

The short answer:

I know of nothing short of plugins or going directly to the database that would give you the time in status in a direct query.

The more interesting answer:

You may be able to use something like the Cumulative Flow Diagram since it shows you how the issues build up in each status and when the issue came into that status.  If you wanted to get real tricky, you could use the REST API to query the report behind the Cumulative Flow Diagram and extract the data that way.  Not sure what your use case is and how much work you are willing to do.