JQL Function for issues in multiple sprints

Question

I would like to look for all issues that have multiple sprint values. (regardless of the sprints are active or closed).

It could be that there are tickets in completed sprints, but were spilled over from sprint 1 to sprint 2. but both sprint 1 and sprint 2 are closed.

It would be optimal to achieve something like this:

Project = ABC and sprint is not empty and sprint has 1 or more values.

Andy Ukasick · Answer

I also need to find a way to do this.

Ryley Fischer · Answer

@Nic Brough -Adaptavist- - What issuefunction can be used to return the items with that have a 'sprint field that contains 2 or more values"? Based on my research I couldn't find anything glaringly obvious that will help in this scenario.

I'm able to create a JQL query that gets me close to what I'm looking for:

project = "Project Name" AND issuetype not in ("Sub Test Execution", Subtask) AND sprint is not EMPTY AND sprint in closedSprints() AND sprint in openSprints()

But this will only return the items in the open sprint that have more than one sprint value. I want to analyze more of our historical values. For instance, I want to see items that could have two sprints that have been closed.

I want to use this data to help coach my team on user story sizing. I want to provide this data to the team so they understand how successful we are based on how far we break down our stories. Here's an example:

We created 100 stories this year that we sized at 8 story points. Of those 100 stories 40 of them rolled over to the next sprint before we completed them.

My organization uses Jira cloud and has ScriptRunner enabled.

Nic Brough -Adaptavist- · Answer

You can simplify the query you have there to just "Project = ABC and sprint is not empty" because not empty means "there are 1 or more values"

The important thing to know about the sprint field is that it contains a list of all the sprints an issue was in when it was ended.  It contains a sprint if you plan the issue into a future sprint, or bring it into the current active one (scope creep) as well, but if you remove an issue from a sprint before it is closed, the sprint is removed from the list.

Also, there are two ways an issue can have more than one sprint value.  The obvious one is "issue open, close sprint, so issue is added to the next sprint" but the other is "issue open, close sprint, issue goes back into backlog, and then goes into a later sprint (not just next, but next +1 or further).

So, yes, I think what you really want is a function that says "sprint field contains 2 or more values"

Rather than try to write that into a query (issuefunction is powerful, but can be slow and it's not obvious to most how to use it), I tend to cheat and use a scripted field.  If you set up a simple numeric scripted field, the  pseudocode is as simple as "return sprint.size()"

If you do that, you can then use a clause like "and number-of-sprints > 1" which is very obvious to people how to use!