How do i write the JQL to find all the stories associated with multiple sprints?

I am attempting to write a query that can help show me stories that have carried over.  In Jira, they are still associated to all of the sprints they have been a part of.  So in this example, I have story X that I added to sprint 1.  Then that story was not completed and it carried over to sprint 2.  And say for whatever reason it then carried over to sprint 3.  I want to write a JQL statement that will show me this so I can apply this to a large swath of projects.

I can write this query that returns the result:

status != Closed and sprint = 1

I can also write this query that would return the same result:

status != Closed and sprint = 2

But what i really want is something like this:

status != Closed and NumberOfSprints > 2

But I can't figure out how to do something equivalent to NumberOfSprints.


5 answers

I have jql that looks to see if an issue is in both a closed sprint and an active sprint

sprint is not EMPTY and sprint in closedSprints() and sprint in openSprints()


You could run this and then combined it with a query that looks for issues in closedSprints() and futureSprints() - this would find any issues moved to a sprint that has not yet started (so it's not "open")


Thank You Jeanne.  How would one find an issue that is only in more than one closed sprints?

Just wondering if you ever found a solution to this?

I am still trying to get a way to filter issues that have been carried forward for 3 sprints or more.


Hello Victor, I really have not found found anything I can do solely in Jira to get these results.  One can export the results of a jql query to excel and work with the data from there, but that is a two step process which our end users would not be as comfortable with.

When time permits, I am going to finish a sql query I am working on to find issues that meet this criteria, then try to build a custom jql tag.  

I'll update this thread should I get a solution.

You can use "in" clause:

status != Closed and sprint in (1,2,7)

In this case, I want to find stories that have appeared in more than two sprints, as an indication of active stories that have been carrying over repeatedly. At this point, I would not know which specific sprints they appear in.

Kyle, Have you had any luck getting the jql? I am looking to do the same thing - find issues that have been carried over into multiple sprints.

I have not had any luck as of yet. I've thought about maybe putting a label on stories that carryover, but that involves a lot of manual intervention and will be very error-prone. But no luck on using JQL to do it yet.

also this answer gives you the list of stories that are in sprint 1 OR, 2 or 7, not AND.

I want to do this too so that I can see how frequently 13 point stories are completed in a single sprint.  Really what I want to see is the frequency that a story is carried over into another sprint by story point estimate (i.e. 90% of 5 point stories are completed in 1 sprint, 90% of 13 point stories get carried over).

This approach is tedious but it works for stories in more than 1 sprint...

find all of the sprints for your board:
https://<your JIRA>/jira/rest/agile/1.0/board/<your board ID>/sprint

using all of the sprint IDs create a query similar to this:

issuetype != Sub-task AND
(sprint = 111 AND sprint in (222, 333, 444) OR
sprint = 222 AND sprint in (111, 333, 444) OR
sprint = 333 AND sprint in (111, 222, 444) OR
sprint = 444 AND sprint in (111, 222, 333))

This does not work for me, I cannot find out all the sprint numbers I want to exclude include. I am working on a project were we have created 4 Jira projects one fore each scrum team and we share work. A issue worked by one team and not finished by them can he handed over to another team based on priority.

This is probably contrary to the post (i.e. a way to do this with JQL), but i do reports on this out of excel. Here are the steps:

1. Do an issue search for: project = "Project Name" and status = Done order by key asc

2. Filter using the Colums filter for "Key" and "Sprint" only. 

3. Export to Excel, remove the top 3 Rows and Bottom row (junk Jira puts in the export) 

4. Save as CSV, open new excel worksheet and import data from Txt, choose the CSV and add Delimeters on Commas. (this will save each sprint label into a new colum for every key) 

5. on the last empty column, use the CountA function to count non-blank cells in as many columns as you have sprint data in (i sometimes have up to 5 :( ) 

The end result is a list of all stories and how many Sprints they touched. You can also add additional fields in step 2 like Assignee if you want to see who has a habit of carrying over stories etc... just make sure the spint field is at the end of the CSV for ease of importing. 

This is smart, and you could save most (all) of the manual work if you used a custom template with the Better Excel Plugin. It supports

It would export sprints separated by comma to a single column and as it supports functions and formulas, you just need to count the sprint names.

This work good with excel.! I just managed to get the result and % of US completed in a sprint and which overflowed.!

But this is required using a JQL.


Just want to activate this thread.

Anyone hopes on this Jira Query?


Hey Sudarshan,

I tried many combinations with JQL as well, but could not figure it out. 

Another easy way my co-worker figured out without having to do the JQL, is to view the burndown reports and see the number of tickets carried forward in each sprint. This is the easiest turn around I guess. 

Suggest an answer

Log in or Sign up to answer
Community showcase
Posted Tuesday in Statuspage

Introducing Statuspage Getting Started guides! First up: What is Statuspage?

Over the next several weeks we'll be sharing some of our Getting Started guides here in the community. Throughout this series of posts, we'd love to hear from customers and non-customers ab...

22 views 2 1
Join discussion

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you