Query: Getting accurate count of when stories are completed in the sprint in which we click Complete

Question

Currently - When stories are incomplete, they are moved to the backlog or the next sprint. The original sprint in which they originated stays attached to the story, so when we are gathering progress data, each Sprint that this particular story has touched gets credit for that story when it moves to "Complete" status. Is there a way to get a query that would allow us to only apply the Sprint completion #'s to the Sprint the story was actually completed, and not the sprints it's previously been assigned to?

Thank you in advance!!

Will Vagle · Answer

Hi @Trudy P Claspill !

Thanks for your reply.

Currently, I am counting the # of stories for each Sprint by status category (including "Complete"). So any story that is tagged with a Sprint designation gets the count. But stories keep their past sprint designations so complete stories appear to be double counted (e.g. The Sprints they were, and the ones they are currently in)

Maybe I'm asking the question in a wrong way - But maybe there's a way to remove the past Sprint designations on a story?

Also - Can you please provide an example of how to use the "between date" function? (e.g. date format, how to get data in between 2 dates?)

Mykenna Cepek · Answer

Welcome to the Atlassian Community, Will. I have several responses for you...

- - - - - - -

If your Jira is using ScriptRunner, then check out the completeInSprint JQL function. It can help identify what issues were completed in a given Sprint.

Note that the documentation isn't very helpful about how to actually use this in JQL (I've suggested they improve the page). Here's the basic syntax to use these ScriptRunner functions:

project = SAMP AND issueFunction in completeInSprint("Sample Scrum board","Sample Sprint 3")

- - - - - - -

I'm not aware of a way to use JQL to definitively determine the Sprint in which an issue was Closed. Note that, technically, there might be multiple such events -- that is, an issue might be Closed, Reopened, and then Closed again.

So to be more precise, you might instead be looking for the most recent Sprint in which an issue was Closed. You can actually never be certain that the data in Jira at this moment is actually the final word on the subject. So be aware that data generated in the past might misrepresent some issue (if they were later reopened).

- - - - - - -

The only time I've done this was outside of Jira. I exported issue data from a project, and the Sprint field expanded to multiple CSV "columns" (to accommodate the maximum number of Sprints any issue participated in). The Sprints seem to naturally be listed in chronological order.

I was able to use Excel wizardry to identify the last non-blank Sprint. Defining the spreadsheet formula to do this was cumbersome, but it ended up doing all the work for me and getting that job done.

- - - - - - -

Hopefully some of that will help you in your quest!

Trudy P Claspill · Answer

Hello @Will Vagle

Welcome to the community.

Can you provide more information about the queries you are executing to gather your data currently?

I think the only way to find the issues closed in a specific sprint is to filter for issues with that sprint association, plus in a "done" state, plus with a ResolutionDate that is within the timeframe of the sprint. You can't do that directly with a JQL query natively in Jira.

You might be able to get such reports with a third part app, but I am not familiar with any specific app that would provide that.