JQL query for JIRA cloud returning list of tickets completed during a specific sprint

[Deleted user]

I want to pull back the tickets completed during a specific sprint.

This is so I can then do some aggregation on them to get metrics using a plugin.

Unfortunately, what used to work, something like:

"status = Done and sprint = <x>" 

as suggested in JQL- Filter completed issues in a specific sprint by category 

no longer works in JIRA cloud, as a given ticket can now have multiple sprints, and there seems no obvious way to pull back the last of these (which would be the one it was completed in).

Hence a query like "status = Done and sprint = <x>"  will now pull in not only tickets completed during sprint X, but also tickets completed during sprint x+1, x+2, etc, that were carried over from sprint X and were once part of it.

Is there way to either:

- Have only a single sprint per field, so when a ticket is moved from one sprint to another the prior sprint is removed. I think this is how things used to work and is intuitive.

- Retrieve the last of the sprints for a given ticket using JQL?

[David Leal]

@[deleted] I was trying to solve a similar problem: Getting information about specific Sprint at the beginning. I don't think JQL will help you. Please take a look at the following question I posted: JQL on a given date does not return the same number of issues as in the Burn-up chart (Sprint start) I responded to myself based on the research I did. It is not easy, but I guess this is the only way to obtain an accurate information from a given Sprint: Using greenhoper JIRA REST API and parsing the information you need.

In your case it is easier to parse it, because you want to find the information at the end of the Sprint. The JSON object returned, provides at the end the final list of issues when the Sprint closed and this information it is very easy to parse comparing with the information at the beginning of the Sprint. Please let me know if it helps. It is not a solution, but maybe you can find a way to obtain the information you want. Let me know if you have any question. 

[Scott Theus]

Hi @[deleted] ,

Try using the function closedSprints()..

closedSprints() IN ("Sprint Name")and status-"Done"

Let me know if this helps.

-Scott

[Deleted user]

That won't help, if sprints X, X+1 and X+2 are all closed, and an issue spanned all 3, it will show up in all the queries.

[Tarun Sapra]

Hello @[deleted] 

You can try something like this

Sprint = <Sprint -id> and Sprint not in openSprints() and resolved < (enter date here which is the completion date of the sprint in question>

By adding "Sprint not in openSprints()" we have removed the issues which might not have been closed in the sprint in question but closed in later sprints and also added the resolved condition to match the completion date of the sprint. 

[Deleted user]

Using the resolved date is an option, though it's very clunky - you need to manually set the date/time to the exact date/time the sprint was closed/started. There's manual effort involved and you can easily get it wrong. So not ideal.

What I really need to do is to split out the sprint field and pull only the latest one.

[Tarun Sapra]

Hello @[deleted] 

"What I really need to do is to split out the sprint field and pull only the latest one."

This is exactly what I do for my reports. But I am using Jira sever and on Jira server it's very easy to do it using scripted fields from the script runner plugins.

ArrayList sprintList = (ArrayList)getCustomFieldValue("Sprint")
int numSprints = (sprintList != null) ? sprintList.size() : 0;

return (numSprints > 0) ? sprintList.get(numSprints - 1).getName() : ""

As provided here

http://www.arsenalesystems.com/docs/display/dataplane/Comparing+Metrics+Across+Sprints

But I am not sure how to achieve this on cloud Jira because Script fields are not supported on the cloud version by Script runner plugin that's why I suggested the approach using "resolved" date which is clunky but is the only approach which comes to my mind.