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

[KyleB]

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.

Thoughts?

[Michael Durbin]

Yeesh!  Extremely disappointing that such a thing still does not exist!  I get wanting to farm this out to the pay plug ins, but even that is failing.  We use Script Runner cloud and it doesn't support this either!  Sure at a point in time when a new sprint is started you can find the stories that just carried over, but once the current sprint is over there is no good way to find stories carried over from one closed sprint to another.

Come on Atlassian, this is a no brainer!

[Jeanne Howe]

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")

[Luke Galardi]

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

[Victor Michalet]

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.

Thanks!

[Luke Galardi]

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.

[Jon Cooper]

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. 

[Aron Gombas _Midori_]

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.

[Sudarshan]

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.

[Roland Muts]

This is infuriating. I need to do some data mining on data. I have 17 closed sprints, 1 active sprint and 5 future sprints..

When i search for

project = "ABP" and sprint = 21 and status = Done

there are multiple tickets with at least two sprints assigned, because the story slipped from one sprint to another. What do i need to do to get just one sprint returned?

How can i get a historic view of what ticket was closed in what sprint, using JQL?

After exporting the search to excel, i see for those tickets multiple columns for the sprint designation.

How can i get just the sprint in which any ticket was closed?

[Raju Mandapaka]

issuetype = story and (Sprint = 3721 and Sprint =4423)

this Jql works for me, it will shows all the stories spill over from sprint 3721 to 4423

[Buck]

Here's my query, hope this helps.

project in (PROJECTNAME) AND status = DONE AND sprint in closedSprints() AND sprint in openSprints() ORDER BY cf[10010] ASC, Sprint ASC

[Vivek Singh]

Please use this JQL

project = "projectid" AND issuetype = story and sprint in openSprints() and sprint in closedSprints()

This will give you all User Stories which all are open in current sprint and been carried forwarded from last sprint or last few sprints.

Then export the result in excel.

e.g. If you are in Sprint 5 and couple of user stories been carried forwarded from Sprint 1,2,3,4, then you will be getting 5 Sprint column separately.

Then put filter and remove blank post filter in Sprint 5 column (Last Sprint Column in this case), this will give you exact number of User Stories which has been started in Sprint 1 and carried forwarded till Sprint 5.

Similarly you can do for other sprints as well

e.g. if any User Story Started in Sprint 2 and Carried Forwarded till Sprint 3 only than Apply filter and remove the blank in 3rd Sprint Column, will give you exact result.

Hope this help, if still not giving the accurate result, please let us know........

For better understanding of your data, you can concatenate the all sprint column in a new column, this will also give you very clear picture of carried forwarded User Stories

[Sudarshan]

Hello,

I use this JQL to get all the issues (except sub-tasks) which were present in closed sprints

project = "Project name" AND Sprint in closedSprints() AND type != Sub-task

with this every release we try to find how many story were involved in more than 2 or 3 sprints, and try to find out why it took so long.!

There is some small amount of excel work involved.!

[Manisha Kode]

I tried to use excel but you get all sprints in different columns - do you have a way to get the last sprint as I wanted to pivot to understand particular type of stories completed in each sprint - if the story is carried over multiple times i am not able to get the last sprint when it was closed easily .

[Sudarshan]

Not sure if i have understood your requirement..!

But these are my thought (this is already in this thread)

• use the JQL to get the required results.
• make sure u have set all the required columns for export (atleast ID, Sprint)
• export to excel.
• now you will have one column in excel named sprints -- it would have values like Sprint1, Sprint2, etc) for multiple sprints or (Sprint1 - if the story is present in one sprint.
• so in this column you split based on the comma (,) to get it more structured.
• now from here with excel functions u can get various graphs and insights.
• my query only gets stories from closed sprints.
• to get the last sprint when a story was closed -- you can add another part to the query as Status = Done
  • this would result stories with status done.
  • so the last sprint value will tell you when the story was closed.

Hope this helps.!!

Mind: I so wish Jira can provide some widgets and graphs inbuilt for this JQL.!

[Manisha Kode]

I use this query to find carried over stories

project = "Projec name" AND issuetype not in ("General Subtask", Retrospective) AND sprint is not EMPTY AND sprint in closedSprints() AND sprint in openSprints() AND status not in (Closed, Complete, Cancelled, Done)

[Vivek Singh]

This is somewhat giving close result.

[Nauman Ikram]

sprint in openSprint()

and sprint in closedSprint()

give the right answer if I want to search issuetype = Bugs with specific statuses.

Thanks.

[Diane Heckman]

Yes, but once you close the current sprint, you can no longer tell which stories carried over into opensprint -1 (the last sprint).  I need to track all sprints in the quarter, and track whether we are carrying over fewer stories as we "improve".  I still haven't found a way in JQL.

[Sudarshan]

Hello,

Just want to activate this thread.

Anyone hopes on this Jira Query?

Thanks.

[sri sura]

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. 

[mike mogensen]

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))

[Dinesh Chaturvedi]

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.

[Volodymyr Krupach]

You can use "in" clause:

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

[KyleB]

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.

[Jeanne Howe]

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.

[KyleB]

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.

[Pieter De Troyer]

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

[zjacobson]

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).