We need the ability to find all issues that have been resolved multiple times.

[Ray Maxwell]

Background,

We used to have a re-work status in our workflows but this has been removed to facilitate better Scrum and Kanban functionality.

We thought we could get the same date using JQL but have run into a problem.

One of the reports that prod management would like is to see issues that have been in a resolved state multiple times.

I have a filter that will give them all issues in Re-work:

project=<project_name> and status in (New,Analysis,Design,Ready,In Progress) and status changed from (resolved,Closed,verified,accepted)

NOTE: our workflow has the following statuses: New, Analysis, Design,Ready, In Progress, Resolved, accepted, verified, and Closed.

This filter handles the Report of issues that are currently being re-worked i.e. have been in Resolved or farther in the workflow then pulled back for evaluation and re-work.

How do I generate a report that will look at how many times an issue has been in the resolved state.

Example: I have an engineer that is constantly under-performing and his work needs to be re-done. I want a report that lists Issues that are being re-opened multiple times.

Haven't been able to find any way to do this.

Help.

Thanks.

[Pablo Beltran]

It's easy to do with SQL for JIRA because it gives you access to the issue change history.

As the SQL query language is pretty flexible there are lot of ways to get the same data, one way might be by using two nested SQL queries:

A) The query below shows the times that the issues of a project have been in a particular state.

select i.key as "Issue", c.field as "Status", count(*) as "Count"
from issues i inner join issuechanges c on c.issueid=i.id 
where i.JQL='project=<project key>' 
group by i.key, c.field

B) now you might want to put the focus on some Status only:

select Issue, sum(Count) as "Total"
from (<embed the SQL query above>) 
where Status in ('resolved','Closed','verified','accepted')

C) So the final query would be:

select Issue, sum(Count) as "Total" 
from 
(
	select i.key as "Issue", c.field as "Status", count(*) as "Count"
	from issues i inner join issuechanges c on c.issueid=i.id 
	where i.JQL='project=<project key>' 
	group by i.key, c.field
) 
where Status in ('resolved','Closed','verified','accepted')

You might also want to use SQL for JIRA Reports & Gadgets extension to show the results on a JIRA Dashboard or the SQL for JIRA Custom Fields extension to show the times that an issue has been in a particular Status by showing the first query results (A) on a custom field table for each issue (and changing the embedded JQL accordingly, of course).