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

GeoOp Accounts October 19, 2016

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
Jobin Kuruvilla [Adaptavist]
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
October 19, 2016

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
Cathal Moane August 29, 2019

As above but use:

BY currentUser()

 if the JQL being executed by each Developer?

0 votes
Pablo Beltran
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
October 19, 2016

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

GeoOp Accounts October 19, 2016

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?

Pablo Beltran
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
October 19, 2016

unfortunately no sad

Suggest an answer

Log in or Sign up to answer