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

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?

2 answers

1 vote

Kinda tricky. Was the status change always done by the assignee at that time? Then you can try this:

status changed FROM "To Do" TO "In Progress" by bob

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.



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

Really appreciate the rapid, detailed answer...unfortunately we are using JIRA Cloud, not server sad

Are you aware of any way we can do this with JIRA Cloud?

unfortunately no sad

Suggest an answer

Log in or Sign up to answer
Community showcase
Published Jan 08, 2019 in Jira

How to Jira for designers

I’m a designer on the Jira team. For a long time, I’ve fielded questions from other designers about how they should be using Jira Software with their design team. I’ve also heard feedback from other ...

886 views 3 9
Read article

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