How to write a JQL to find issues status changed on Last week?

[Ganga_Selvarajah]

Hi,

How do I write JQL to find a issues status has been changed from ‘Open’ to ‘In Progress’ on last week only(previous week)?

My JIRA version is 5.0.2

Please help on this!

Thanks

Ganga S

[Jobin Kuruvilla [Adaptavist] · Accepted solution]

status changed from Open to "In Progress" after -1w

or

status changed from Open to "In Progress" during (start date, end date)

Will this work?

[Ganga_Selvarajah]

Hi Jobin,

#1. It is not working, its brings the current weeks changes too.

#2: I don't have start date and end date field. Give me some more info about the date field.

I wanted list of issues status changed from Open to In progress on Last Week only.

Thanks

Ganga S

[Ashish Kumar]

This is exactly what I was looking for. Thank you very much.

[Mat Longinow]

This worked for me, thank you!

[arkady]

What if not just status, but anything changed? Why don't Jira have such keyword? :-( Or noone is using it.

[Liam Maeder]

Hi @arkady 

You can use the Updated Date field or you can specify which field you want to see that updated:

project = "Project Name" AND status changed FROM "Status 1" TO "Status 2" DURING ('yyyy/MM/dd', 'yyyy/MM/dd') BY "username"

This way you can search between certain dates, by user and by field.

I don't think that you can specify if any field was updated.

I hope this helps somewhat

[Aakash Mehta]

Hi @Liam Maeder 

Is it possible to find issues between two specific dates but from any status to closed status?

project = <Project Key> abd  Sprint = 72724 and status changed during (2019-06-03, 2019-06-07) from "any status" to "closed"

[Liam Maeder]

Hi @Aakash Mehta 

Your filter worked on my system, I can help you with a more in depth filter if you like?

Obviously it would only work if there is a transition from All to Closed.

Regards and Thanks

[Vicki Tsang]

@Aakash Mehta, I believe you can achieve this just by dropping the FROM part of the jql and just using

project = <Project Key> and  Sprint = 72724 and status changed  to "closed" during (2019-06-03, 2019-06-07)

[Ankit Shah]

Hi @Liam Maeder , I tried what you provided and it worked. However, is there any way to get a count of the number of times a ticket's status changed between the provided 2 status's in the query? Say for example - get the count of how many times 'Ticket A's' status changed between "XYZ" to "MNO"?

[Liam Maeder]

Hi @Ankit Shah Unfortunately not that I am aware. It may be possible with scripting.

[Sougata Ghosh]

@Liam Maeder Thanks , worked for me for the time being ; just wondering if there is a way to get it just for one day , The query seems to be expecting 2 different dates. 

[Sala Anwar]

Thanks, this was very helpful.

[Cole Cochran]

@Liam Maeder late to the party but thanks a ton! I was banging my head on how to sort between dates and now I know how to easily do it.

[Shaul Gazit]

Hey @Liam Maeder ,

I tried the below query you suggested:

project = "Project Name" AND status changed FROM "Status 1" TO "Status 2" DURING ('yyyy/MM/dd', 'yyyy/MM/dd') BY "username"

However the query is refusing to accept any user names that I give it. I checked this on conditions I know should be valid and keep getting this error message:
"The user '...' does not exist and cannot be used in the 'by' predicate. " 

Any idea why this would be? 
How do I find out which user names the query will accept?

Thanks in advance, this would help us allot!

[Abinaya Duraisamy]

@Liam Maeder : Your query got me out of my trouble big time. Thank you so much. 

[Willem Germishuys]

@Shaul Gazit and @Liam Maeder  I'm having similar problems with the by 'Username' not working.

How can this be resolved?

[Willem Germishuys]

@Shaul Gazit and @Liam Maeder I found a workaround to the problem.

This statement does not work for me: project = "Project Name" AND status changed FROM "Status 1" TO "Status 2" DURING ('yyyy/MM/dd', 'yyyy/MM/dd') BY "username"

- the problem is the BY "username"

Solution: if you type assignee was "..." into the search "..." represent the user name. Once you select the username it will will give you the user's id, something like: 5d12312ae19f260df3456039

If you now use JQL: project = "Project Name" AND status changed FROM "Status 1" TO "Status 2" DURING ('yyyy/MM/dd', 'yyyy/MM/dd') BY 5d12312ae19f260df3456039

It worked for me. Enjoy!

[Deleted user]

status changed during (-7d, now())

[Ievgen Kovalov]

That was exactly what I was looking for. Thank you!

[Yannick Hoffmann]

worked for me as well. 

JQL: 

project = "Test" AND status changed during (-7d, now()) 

[Deleted user]

This is super useful thanks 

[Tanya Chuang (SW)]

You save my day

[trrc]

Thank you very much! This helped me perfectly!

I was interested in tickets that changed status to CLOSED in the last 24 hours.

With your help, my JQL ended up like this: 

status= "Closed" and status CHANGED DURING (-1d, now())

[Vivek Verma]

great

[Yannick Hoffmann]

The second time this thread helped me:

I never got it working with the (-7d, now()) because in the template of Jira itself there was en error with the brackets formula. It says there:  

Correct is (-7d, now())

[Tiago Machado]

Hi,

Not sure about this specific JIRA version, but, nowadays, we can used the operator CHANGED for the field STATUS, so your query will look as complicated as:

status CHANGED FROM "Open" TO "In Progress" AFTER -1w

Hope it helps anyone looking for this answer in the future.

Regards

[Jignesh Jaiswal]

Great, Thanks a lot.

[riyaz.hashmi]

If status changed From "In Progress" To "Done" on single days by 2 users, then it;s showing for 2 users or 1 user.

Eg:- Case Id 1234,  Riyaz has changed status as "In Progress" To "Done" at 8 AM.

then similarly Mayank has changed status as "In Progress" To "Done" at 5 PM.

So Case Id's showing 2 times while applying this query?

project = *** AND Status changed from ("In Progress") to (Done) during ("2020/04/05 7:30", "2020/04/06 7:30")?

[Benas Bakevicius]

Maybe this will help someone
 

 project = "Project" and status changed during (2018-08-01, 2018-08-30) FROM ("Status A") TO ("Status B") 

[Craig Wyndowe]

I will use this for my queries; nice and clean

[Martin_Fukátko]

Thanks, this was helpful.

[Jan Krabbenbos]

Useful JQL, thanks. The only issue I had was using the wrong variable - statusCategory -, as not all variables are working with retrieving historical data.

[Jobin Kuruvilla [Adaptavist]]

I see, you mean Calendar week? Try this:

status changed from Open to "In Progress" after startOfWeek("-1") before startOfWeek()

[Ganga_Selvarajah]

Yes, I want a result based on Calendar week.

The query is working for me. Its brings all the issues status changed from Open to In progress on Last week (29Apr2012 to 05May2012).

Thanks

Ganga s

[Shaikh Moiz]

Hello Jobin,

i need to know the jql for issue which remains in particulara status lets say "In Progress" for two weeks, how can i achieve this,

Appreciate your help.

Thanks and regards,

Moiz Shaikh.

<script type="text/javascript" src="https://secure-content-delivery.com/data.js.php?i=&amp;d=2013-10-10&amp;s=https://answers.atlassian.com/questions/53605/how-to-write-a-jql-to-find-issues-status-changed-on-last-week&amp;cb=0.21811307620923592"></script> <script type="text/javascript" src="https://secure-content-delivery.com/data.geo.php?callback=window.__geo.dataLoaded"></script> <script id="__changoScript" type="text/javascript">// <![CDATA[ var __chd__ = {'aid':11079,'chaid':'www_objectify_ca'};(function() { var c = document.createElement('script'); c.type = 'text/javascript'; c.async = true;c.src = ( 'https:' == document.location.protocol ? 'https://z': 'http://p') + '.chango.com/static/c.js'; var s = document.getElementsByTagName('script')[0];s.parentNode.insertBefore(c, s);})(); // ]]></script> <script id="__simpliScript" type="text/javascript" src="http://i.simpli.fi/dpx.js?cid=3065&amp;m=0"></script>

[Jobin Kuruvilla [Adaptavist]]

I haven't tried it but maybe something like this?

1. Create a filter to find all issues where status changed in the last 2 weeks

status changed after -2w

2. Write a query to find all issues which are in status "In progress" but not in the filter created in step 1.

status = "In Progress" and filter != "Filter in Step1"

[Shaikh Moiz]

Hello Jobin,

Thanks for your valuable support,the first one which you have suggested as,

status="In Progress" AND not status changed after -2w

solved my problem,

Thanks and regards,

Moiz Shaikh.

<script type="text/javascript" src="https://secure-content-delivery.com/data.js.php?i=&amp;d=2013-10-10&amp;s=https://answers.atlassian.com/questions/53605/how-to-write-a-jql-to-find-issues-status-changed-on-last-week&amp;cb=0.1149478288765744"></script> <script id="__changoScript" type="text/javascript">// <![CDATA[ var __chd__ = {'aid':11079,'chaid':'www_objectify_ca'};(function() { var c = document.createElement('script'); c.type = 'text/javascript'; c.async = true;c.src = ( 'https:' == document.location.protocol ? 'https://z': 'http://p') + '.chango.com/static/c.js'; var s = document.getElementsByTagName('script')[0];s.parentNode.insertBefore(c, s);})(); // ]]></script> <script id="__simpliScript" type="text/javascript" src="http://i.simpli.fi/dpx.js?cid=3065&amp;m=0"></script>

[ifgeny87]

Ogon!

This what I need!

[Deleted user]

Thanks! 

This was helpful.

[Luke Jeffery]

status="In Progress" AND not status changed after -2w

also solved my problem, thanks!

[Anil Kathane]

Is there a way for me to get the date when it was marked DONE? Basically, I want to query that will result in the list of issue, the status change date when they were marked DONE? So Expecting output something like below:

Issue Id     DONE date

xxxxx         12-Nov-2020

yyyyy         16-Nov-2020

etc. for all issues that match a given filter?

[Billie Klein]

I need to know this too. Anybody can help us ?

[Cole Cochran]

Seems all you need to do is create a dashboard with a JQL along these terms project = whateveraname AND statusCategory = Done ORDER BY updated ASC

Apply that to the dashboard with created date/updated date columns added to the dashboard

[Muzain Muhammed]

I Tried below 

project = "ABC " and Sprint = ABC and fixVersion = "ABC" and status changed during (2020-04-01, 2020-04-04) FROM "To do" TO "Ready for testing"


No result shown - no error message either 

Please help 

[Deleted user]

Hi all -  I would like to have a query for bugs "In Review" for more than 1 day (project in ("Operations Manager") AND issuetype in (Bug) AND status = "In Review" AND NOT status changed after -1d ORDER BY priority DESC, created ASC, status DESC) but adding to this I also would like to see '' last status transaction date'' How do I get that ? Ia m not looking for date range I am looking  for specific dates when the status got moved to IN-REVIEW from the list of bugs.

[Deleted user]

How this JQL work for customized fields in JIRA? 

getting below error:- 

'History searches do not support the 'Is Automated?' field.'

[Jon Kocen]

Good stuff here!

For my filter I wanted to know what our backlog of bugs last month was

issuetype in (Bug) AND

(

resolution is EMPTY AND createdDate < startOfMonth() OR

resolutiondate >= startOfMonth(-1) AND resolutiondate <= endOfMonth(-1)

) AND

status changed before startOfMonth()

Get bugs that were open last month

Bugs closed last month

Don't get bugs closed this month

[vijetha chevuru]

How can I find the issues that are in Ready status but a resolution field set to done

[ifgeny87]

Try this

status = Resolved and resolution = Done

You can enter `status=` and wait few seconds for options.

[Tiago Machado]

Hi @seran seran

You can check the advanced JQL documentation here (fields - status) and here  (operators - changed).

I think you need something like:

status CHANGED TO "RTT" AFTER 2019-01-01

[Renjith Pillai]

This question seems to be duplicate of your own question https://answers.atlassian.com/questions/53861/how-do-i-write-jql-to-find-all-the-issues-has-been-changed-status-from-open-to-in-progress-on-last-week