nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

How can I find an issue that is in multiple sprints?

Luke Galardi

I am searching for a means, preferably jql, to find all issues in a project that are in more than one sprint. Whether open sprints, closed sprints or future sprints - if there is more than one sprint associated with the issue I'd like to query to find it.

Maybe this is beyond the scope of jql and there is a plug-in to find it or I will need to write sql to do so.

Accepted answers

All comments

AnnWorley

Do any of these suggestions help?

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

Luke Galardi

Close, but it does not show issues that are in 2 or sprints that are closed sprints.

I have an issue, for example, that is in 4 closed sprints. I cant quite figure out how to make it show using jql. Maybe this require a sql query???

Luke Galardi

Thank You, Ann!

AnnWorley

select distinct count(cv.issue), p.pkey, ji.issuenum
from customfieldvalue cv
inner join customfield cf on cf.id = cv.customfield and cf.customfieldtypekey='com.pyxis.greenhopper.jira:gh-sprint' 
inner join jiraissue ji on ji.id = cv.issue
inner join project p on p.id = ji.project 
group by cv.issue, p.pkey, ji.issuenum
having count(cv.issue) > 1

This should give you a start - it ran on my instance and reported as expected.

Luke Galardi

Hi Ann, Thank You! I will give it a shot now.

Charles Fisher_okhoq7

So - I have the same question, and here's a 'poor man's' way to get to the information. In my case I wanted to see all issues closed in the previous release and then determine how many of those issues took more than one sprint to close.

I created a query that met the criteria (in the last release, closed) and also included the Sprint column in the results. The Sprint colu,mn includes all sprints that each issue was in, separated by commas.

I exported the results to Excel.

In my case I just wanted to count the sprints per issue, so in Excel, I split the Sprint column into separate columns by the comma delimiter.

Then I was able to filter on the split sprint columns to see how many issues took 2 sprints, 3 sprints, etc.

Tedious, but not as tedious as counting by hand.

Ajay Mishra_o1rcm3

where do you write these SQL? The way we write JQL for filters doesn't accept this sql, is that right?

Leo Shklovskii

Assuming your sprints are incrementing in a sort (ie. Sprint 5, Sprint 6, etc... although this may work just based on Sprint ID rather than sprint name (I'm not sure how JIRA sorts)) you can do something like this:

status != Done AND sprint in closedSprints() AND sprint in openSprints() ORDER BY Sprint ASC

Make sure the sprint column is showing and you'll see the tickets that have been dragging the longest at the top of the results (because they belong to the oldest sprint).

Guy LaRochelle_uth2t0

Same here, how to you use this SQL if you're not an admin of the Jira instance?

Andrew Newbury

A colleague at our organisation came up with this simple yet elegant board filter

issueFunction in previousSprint(<board number>) AND sprint in openSprints()

eg. where 17703 in this instance is my board ID

Why? We've exceeded the preset parameter for the total number of closed sprints in our instance to allow closedSprints() to return a value, raising that could have a potentially significant performance hit with a "badly" written JQL.

Jared Dohrman {ANZ}

Noting that this requires ScriptRunner and assumes you're running the server/data centre version of Jira.

Kunal Kumar_kn9ukt

This will only give the list of stories which were moved to a next sprint after spilling over and will exclude the ones which were moved to Backlog. A better JQL would be :

issueFunction in previousSprint(<board number>) AND status =! Closed

Victor Moroz_wu16gh

Thank you for this helpful query.

Do you know if it is possible to find the issues that were in progress for multiple sprints and are now closed?

I want to make a list of issues that were and/or are living for multiple sprints so we can analyze them with the team.

the query

sprint in closedSprints() AND sprint in openSprints()

finds only the issues that are in progress in the current sprint and were in progress in a previous sprint.

Andrew Newbury

Agreed: it's difficult to identify spillover so I wrote a PowerShell script, available on GitHub, to do this. It queries a specified Jira project to find work items (stories, tasks, and bugs) that have been:

Modified within a user-defined timeframe
Worked on across multiple sprints (indicating spillover)

Results are then displayed on screen and exported to a tab-separated text file for further analysis in Excel or similar apps.

Agreed #2: it's difficult to manage spillovet so I wrote Mastering agile spillover analysis and strategic solutions. This describes what teams can do to reduce spillover.

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

issue

sprint