JQL for finding unique sprint where issue was closed

[Amy Bailey]

Hi there.  We are using JIRA cloud to manage several scrum teams.  We allow multiple sprints to be used in the sprint field in case an item crosses sprints.  But that presents a problem when trying to create a report that looks for all issues closed within a sprint.  The basic JQL "sprint = 74 AND status = Closed" will pull the issue that spanned sprints 74 and 75.  Even though the issue was marked Closed in sprint 75.  

How do I query for issues that were transitioned to a particular status within a particular sprint? 

Note - I've looked at transition field that allows you to query transitions within date ranges, but that is not helpful as our scrum teams have different sprint dates.  We'd have to keep a master table somewhere with all the sprint begin/end dates and change the query for each team.   

If anyone has an idea, I'm all ears.  Thanks!!

[Mario Carabelli · Accepted solution]

@Amy Bailey

If you want to get all issues that were closed in sprint 74 you can get them in my opinion by simply using:

Status = closed and sprint =74 and Sprint != 75

This way you exclude the issues you took into the next sprint (which by definition are not closed in the previous sprint)

With kind regards

Mario

[Amy Bailey]

Thanks Mario.  We could certainly do this.  It would involve creating quite a number of queries though to cover the whole fiscal year.  But as you said, it would at least get us the data we're looking for.  Thank you.

[Amy Bailey]

Actually... this won't work.  Because it will discount the items that are assigned to both sprints.   so if an issue belongs to sprint 74 and 75 and is closed, this query will not count it.   I'm trying to find the issues that were closed within a particular sprint. 

Thank you for the suggestion.

[Mario Carabelli]

I think I didn't explain my idea good enough or I misunderstood what you wanted to achieve.

If you take issues into the next sprint there are 3 cases of issues.

Issues which are associated with only sprint 74
Issues which are associated with sprint 74 and sprint 75
Issues which are associated only with sprint 75

Under the assumption that issues only belong to category 2 if they weren't finished in sprint 74 they were not closed in sprint 74 either.

Thats why I assumed if you want only the issues which are closed in the particular sprint 74 you need to discount the issues of case 2 to get all the issues of case 1 and nothing more.

Do you move closed issues to the next sprint in special occasions?

Or do you want smth else which I didn't understand?

With kind regards

Mario

[Amy Bailey]

No... your understanding is absolutely correct.  And I will try your query.  After your additional explanation it makes more sense to me now. Thank you!  

[Mario Carabelli]

Glad that i could help. Let me know if it worked like you imagined it.

With kind regards

Mario

[Amy Bailey]

Many thanks Mario, 

This worked perfectly.

[Sunita Ranka]

It solved my problem too :) 

Thanks @Mario Carabelli 

[Jakub Hanak]

Hello @Amy Bailey,

do you have ScriptRunner? If so, try to use this JQL:

issueFunction in completeInSprint ("Board name", "Sprint name")

Best regards,

Jakub

[Amy Bailey]

Hi Jakub,  We do not have ScriptRunner.   I thought perhaps this was straightforward enough that we wouldn't have to buy a plugin to solve it.... but I guess not??

[Jakub Hanak]

There is no need to be rude. ScriptRunner is commonly used add-on, used not just for JQL, but has many usefull functions. I wouldn´t suggest to buy this plugin just for this issue either. 

[Amy Bailey]

Hi Jakub.  Sorry, not being rude.  Just thought this was a common ask and something that JIRA would handle by default. 

[Jakub Hanak]

Alright then. 

Maybe try something like this

sprint = "sprintID" and resolution != empty

This should pick just issues, where resolution is not empty, therefore they shoud be closed.

Let me know if that works for you. 

[Amy Bailey]

Hi Jakub, This doesn't work because the issue spanned sprints and so has both sprint 74 and 75 assigned.  So the same issue will be counted in each of these 2 queries: 

sprint = '74' and resolution !=empty

sprint = '75' and resolution !=empty

This counts the same issue twice.  We're trying to find all issues that were closed in a particular sprint.  So if an issue has sprint 74 and 75 assigned, but it was open at hte end of sprint 74 and closed in 75, i want a query that won't pull this issue when I query issues closed in sprint 74.  It should only show up when querying issues closed in sprint 75.

Thanks!