How get only the last occurance with JQL status changed from to?

[Maik Winter]

I tried to write a JQL to get only the latest occurance of a status change.

Example workflow sequence 1:

Issue Create -> Open -> Classified -> Support Investigating -> Waiting for Customer -> Support Investigating -> Provider Investigating -> Waiting for Customer -> Resolved -> Closed

Example workflow sequence 2:

Issue Create -> Open -> Classified -> Support Investigating -> Provider Investigating -> Waiting for Customer -> Provider Investigating -> Support Investigating -> Waiting for Customer -> Resolved -> Closed

The JQL's are should only find issues in the status "Waiting for Customer" with only the latest occurance from "Support Investigating" to "Waiting for Customer" / from "Provider Investigating" to "Waiting for Customer".

My actual two JQL's are find the same issue, because the filter does not consider only the last occurance :

project = itsd and status = "Waiting for Customer" and status changed FROM "Support Investigating" TO "Waiting for Customer"

project = itsd and status = "Waiting for Customer" and status changed FROM "Provider Investigating" TO "Waiting for Customer"

Any ideas?

Regards,
Maik

[Nicolas Bourdages · Accepted solution]

I would use workflow post-functions to store a value for the previous status in a Latest Status custom field. In your example, when using the transition from "Provider Investigating" to "Waiting for Customer", the post function would set that custom field to "Provider Investigating".

I think sometimes it's not about bending JQL to your will, but marking issues in a way that can be picked up by JQL.

[Ignacio Pulgar]

+1ed your solution 

[Nicolas Bourdages]

Cheers 

[Maik Winter]

Thanks Nicolas, this was the simplest way to cover the requirement

[Nico n]

thanks for the idea, unfortunatey, i don't find a free way to update a custom field in the postfunction. There is jira suite utilities but it is not free.

Do you know any alternatives? 

thanks

Nico

[Ignacio Pulgar]

JQLs work like a WHERE clause added on a SQL starting by "SELECT * FROM issues_table WHERE ... <JQL>". So, a JQL will always retrieve just a list of issues that matches the specified conditions.

Therefore, not being able to query the table of historical status changes directly, you will not be able to obtain its last change through built-in JQL resources. (However, look at Nicholas Bourdages answer for a good solution)

Provided the issue has historically changed to "Waiting for Customer" from both statuses, you can just add some extra conditions, with BY <username>, or with BEFORE <date> and AFTER <date>.

For example, this JQL returns all ITSD issues on "Waiting for Customer" status which status you had ever changed from "Support Investigating" to "Waiting for Customer" during the last week:
 

project = ITSD and status = "Waiting for Customer" and status changed FROM "Support Investigating" TO "Waiting for Customer" BY currentUser() AFTER startOfDay(-7)

Of course, this approach assumes that you know some relevant things beforehand, like who is the user whose changes you are interested in, or the date range on which the change you are looking for occurred, so this is not a solution, but a workaround.

Hope it helps.

[Vasiliy Zverev]

There is more hardcore solution: use scripted field via ScriptRunner to analise chnage history. 

Here is some code to get last status:

import com.atlassian.jira.component.ComponentAccessor
import com.atlassian.jira.issue.changehistory.ChangeHistory
import com.atlassian.jira.issue.history.ChangeItemBean


PreviousStatus previousStatus = new PreviousStatus(0, issue.getStatusObject().getName())

for(ChangeHistory changeHistory: ComponentAccessor.getChangeHistoryManager().getChangeHistories(issue)) {
    for(ChangeItemBean changeItemBean: changeHistory.getChangeItemBeans()) {
        if (changeItemBean.getField().equals("status")) {
            previousStatus.setNewStatus(changeItemBean.getCreated().getTime(), changeItemBean.getFromString()) ;
        }
    }
}

return previousStatus.getStatus();

class PreviousStatus{
    private long dateInMills;
    private String value;

    public PreviousStatus(long _dateInMills, String _value){
        dateInMills = _dateInMills;
        value = _value;
    }

    public setNewStatus(long _dateInMills, String _value){
        if(_dateInMills > dateInMills){
            dateInMills = _dateInMills;
            value = _value;
        }
    }

    public String getStatus(){return value}
}