JQL - select user when issue transitions from "To Do" to "Peer Review"

GeoOp Accounts

In scrum board issues transition from To Do/In Progress/Peer Review/Done.

The typical issue flow is as follows:

1. Bob Jones assigned issue, status = To Do.
2. Bob Jones transitions issue from To Do to In Progress
3. Bob Jones transitions issue from In Progress to Peer Review, issue now assigned to Jim Smith
4. Jim Smith transitions issue from Peer Review to Done.

JQL works fine for (assignee was "Bob Jones" and status changed FROM "To Do" TO "In Progress")

However JQL will pick up the same issue when I run (assignee was "Jim Smith" and status changed FROM "To Do" TO "In Progress")

Why? Because at some stage the one issue "WAS" assigned to both Jim Smith and Bob Jones.

I am trying to write JQL for each individual developer to select ONLY the issues transitioned from In Progress to Peer Review by that developer.

Ideally I would like a "WHEN" clause added, eg: (assignee was "Bob Jones" WHEN status changed FROM "To Do" TO "In Progress")

Is there some other way to write the JQL to extract the information I am after?

Contact Kintosoft

The SQL for JIRA plugin  is able to resolve it because you can transform your JQL into a SQL, apply filters which are not supported in standard JQL and the convert back your SQL for JIRA query into a new JQL again.

Example:

 

select i.key 
from ISSUES i 
    inner join ISSUECHANGES c on c.issueid=i.id 
where JQL = ' assignee was "Jim Smith" and status changed FROM "To Do" TO "In Progress" ' 
    and c.field='Status' 
    and c.fromvalue != 'Peer Review'

 

How it works:

The SQL engine performs your JQL and it gets just the same "wrong" results, of course. Then it performs a JOIN to the ISSUECHANGES table and gets the history for each issue and excludes all of them which the status had the 'Peer Review' as orign value in the workflow transition:

c.fromvalue !='Peer Review'

You can perform (and modify if it is necessary) the SQL for JIRA query above in the built-in SQL console to explore the results in a visual and friendly way until it returns what you want.

 

Finally transform the SQL for JIRA into a JQL again by using the sql built-in JQL function.

 

issue in sql("<your SQL for JIRA query here>")