Need a way to identify closed stories that moved through multiple sprints before completion.

[Prasanna Subramaniam]

Is there a way to retrieve the list of stories that are already closed but had moved across multiple sprints before they were finally completed?

[Paul Glantschnig _Appfire_]

Hi @Prasanna Subramaniam,

Welcome to the community!

Unfortunately, Jira doesn't offer a native JQL function that returns the sprint count for an issue. The sprint in closedSprints() query will return stories assigned to completed sprints, but as you noticed it doesn't distinguish between single-sprint and multi-sprint stories.

The most common native workaround is the automation approach that @Marc -Devoteam- and @Gor Greyan already suggested:

1. Create a number field (e.g., "Sprint Count").
2. Set up an automation rule triggered by Field value changed > Sprint. In the action, use Edit work item with this JSON body:

{
  "fields": {
    "customfield_XXXXX": {{issue.sprint.size}}
  }
}

Replace customfield_XXXXX with the ID of your number field. The smart value {{issue.sprint.size}} returns the total number of sprints the issue has been assigned to.

3. Once the field is populated, you can query with JQL:

type = Story AND status = Done AND "Sprint Count" > 1

If you can't create custom fields, a variation is writing to labels instead (as you were exploring). The JSON for that looks like:

{
  "update": {
    "labels": [
      { "remove": "SL_{{issue.sprint.size-1}}" },
      { "add": "SL_{{issue.sprint.size}}" }
    ]
  }
}

This removes the previous label and adds the updated one. You can then filter with labels = "SL_2" OR labels = "SL_3" etc.

One caveat with both approaches: the automation only starts counting from when the rule is created, so it won't backfill historical sprint data.

Hope this helps,

Paul

[Paul Glantschnig _Appfire_]

One more thing that might help here, @Prasanna Subramaniam. You mentioned not being able to create custom fields, which makes the automation approach tricky.

If you're open to solutions from the Atlassian Marketplace, JXL for Jira has a built-in "Number of Sprints" smart column that does exactly this, no custom fields or automation rules needed. It's a computed column that counts how many sprints each issue has been part of, and you can filter directly on it to show only stories with a count greater than 1.

JXL also has related sprint smart columns like First Sprint and Last Sprint, so you can see at a glance where a story started and where it ended up. Here's how the sprint count column looks:

Since all of this works on historical data already stored in Jira, it covers your existing stories too, not just future ones.

Best,

Paul (I work for the team that builds JXL)

[Natalia_Kovalchuk_SaaSJet_]

Hi @Prasanna Subramaniam!

This is a great question, and honestly, it isn't so easy to do this using native Jira functionality.

I recommend trying Issue History for Jira (Work Item History) app from SaaSJet team to retrieve the list of your stories that were already closed but moved across multiple sprints before they were finally completed. 

Here is how to do this:

1. after the app installation, go to its Table View and set the necessary filters (e.g., by space, date range, etc.).

2. if you need to get the list of closed tasks, you can use the Status filter (e.g., to choose only the tasks that are in the status Done). 

3. in the Sprint column, you’ll see all the sprints each task went through before being completed. This makes it easy to identify stories that moved across multiple sprints.

4. if needed, you can download such a report in Excel or CSV format. 

Here you can find more detailed info: Review sprint history for Jira tasks

Hope it will be helpful!

[Gor Greyan]

Hi @Prasanna Subramaniam

Welcome to the Atlassian Community!

Adding, what @Marc -Devoteam- said.

That will also return those stories, which were only in one sprint.

If you need an exact stories, that was gone through multiple sprints.

You can add a number field in your stories, and run an automation with this smart value, which will write in that custom field the sprint count, through which the story is gone.

{{issue.sprint.size}}

[Prasanna Subramaniam]

Dear @Gor Greyan , Is there a different option, I cannot create custom fields and smart values, just by JQL

[Gor Greyan]

Dear @Prasanna Subramaniam

There is no native JQL, which I could find for sprint count, and ScriptRunner doesn't have any function that can return the sprint count for that issue.

Only my mentioned option, I think, can work. Keeping the sprint size in the custom field, then try to retrieve it via JQL, searching that custom field, which is greater than 1.

[Prasanna Subramaniam]

Deae @Gor Greyan ,

Agreed. Instead of using a custom field, I’m trying to generate this value through a label. However, I’m not getting the expected output. I’m simply trying to append the sprint count with the prefix "SL_".

Example: A story that has spanned across 2 sprints should produce:

SL_2

But the output I’m currently getting is:

{{"SL_".concat{{issue.customfield_10020.size}}}}

Where am I going wrong?

[Gor Greyan]

Dear @Prasanna Subramaniam

customfield_10020 is the number field, which you have created and kept there, the {{issue.sprint.size}}?

[Prasanna Subramaniam]

Dear @Gor Greyan ,

customfield_10020 - is the field used for Sprint. Its a Alphanumeric filed

[Gor Greyan]

Dear @Prasanna Subramaniam

You can just use this without a custom field. Just Edit work item and put the following JSON.

{
     "update": {
             "labels": [
{
"add": "SL_{{issue.sprint.size}}"
}
]
}
}

[Marc -Devoteam-]

Hi @Prasanna Subramaniam 

Welcome to the community.

You cold use JQL:

type =story and status = closed and sprint in closedSprints()

This will get any stories in status closed and in relation to closed sprints.

The sprint field will show the sprints the story has been in.

[Prasanna Subramaniam]

Dear @Marc -Devoteam- , Thanks for your response. 

My ask is slightly different. Using closedSprints() returns stories that were closed in past sprints. However, what I need is a way to identify stories that actually went through multiple iterations before getting completed.Example:

1. Story A‑123 took 2 sprints to close
2. Story A‑234 took 3 sprints to close
3. Story A‑345 took 1 sprint to close

With the existing query, all three stories appear in the output. But my requirement is to fetch only the first two stories — i.e., stories that traversed more than one sprint before closure.

[Marc -Devoteam-]

Hi @Prasanna Subramaniam 

Then you have to create a custom field of type number, as suggested by @Gor Greyan 

Then create an automation when the sprint field is updated this number field updates from 0 to 1 and from 1 to 2 and so further any time the sprint value changes.

then you could a this field in your JQL statement.

[Rahul_RVS]

Hi @Prasanna Subramaniam 

Welcome to the community !!

You can use Jira Rest Api's to pull this information and build your own custom solution.

However if you need detailed reporting to track changes for multiple issues, you may want to have a look at a mktplace app for the same.

We have built an app to extract changelog data in a simple and easy to use interface. The data can be exported to a CSV file as well.

It provides complete details of who changed the data, what was changed and when. 

Example below shows Sprint changes for the issues

Issue History Reports

Do give it a try.

Disclaimer : I am part of the app dev team

[Bill Sheboy]

Hi @Prasanna Subramaniam -- Welcome to the Atlassian Community!

As others have noted, there is no built-in way to do this with JQL.

The answer to your question depends upon how often you need this information and your willingness to spend money / effort:

• need this one time
  • use JQL to identify the work items with a value in the sprint field, export the results to a spreadsheet, and use spreadsheet functions / features to detect the items in multiple sprints
• need this often
  • have money to spend
    • investigate marketplace apps to:
      • extend the features of JQL,
      • perform the reporting, or
      • add dashboard gadgets with field count rollups
  • have time / effort to spend
    • use automation rules, either with or without a custom field
      • without a custom field: report the items at the end of each sprint which were completed with multiple values in the sprint smart value, and send an email to interested stakeholders
      • with a custom field: as others have described, add a custom field for the "sprint count" and set the value when a work item completes

Additionally, consider how to handle errors where a work item was put into a sprint incorrectly and then removed.

Kind regards,
Bill