query to show when ticket is moved to in progress

[Farid H_ Nia]

I need a search/query to show when a user story is moved from "To Do" into "in Progress" and how long before it was closed/resolved/done for the month of say January. Thanks

[Valeriia_Havrylenko_SaaSJet]

Hi @Farid H_ Nia 
Welcome to the Community!

If you are interested in time reports and ope to add-ons, option for you can be Time in Status. To show report when a user story is moved from "To Do" into "in Progress", just:

1. Choose Status Entrance Date report
2. Filter by Project, Assignee, Sprint etc.
3. Filter issues by Data Ranges
4. Get your data!

To know how long before issue was closed/resolved/done for the month of say January, just:

1. Choose Time in Status report
2. Filter by Project, Assignee, Sprint etc.
3. Filter issues by Data Ranges
4. Get your data!

You can also book a live demo - we'll show you the application inside out and answer all your questions.

Add-on also developed by my team.

I hope you find this helpful 🚀

[Farid H_ Nia]

Thank you Valeriia, I will review and try

[Valeriia_Havrylenko_SaaSJet]

@Farid H_ Nia happy to help!

[Ayça Erdem_OBSS_]

 Hello @Farid H_ Nia 

Welcome to the community!

There are a few ways to solve this,check out the details below

• Using JQL for Status Changes (Basic Query Approach)

You can use JQL to find when a user story moved from "To Do" to "In Progress" and was later completed within a specific timeframe, such as January.

1. To find issues that transitioned to "In Progress" in January:
   jql

   status CHANGED FROM "To Do" TO "In Progress" AFTER "2024-01-01" BEFORE "2024-01-31"
2. To find issues that were completed (Done/Resolved) in January:
   jql

   status CHANGED TO "Done" AFTER "2024-01-01" BEFORE "2024-01-31"

Limitation: JQL does not calculate time spent between these statuses. It only returns issues that match the criteria.

• Using Timepiece – Time in Status for Jira by our team at OBSS

Another solution that,you can use Timepiece – Time in Status for Jira. This tool provides:

Transition Dates Report – Shows exactly when an issue moved from "To Do" to "In Progress."
Status Duration Report – Calculates how long the issue stayed in "In Progress" before it was completed.

Here’s an example report showing the exact date when stories moved from "To Do" to "In Progress":

Here is the how long issue stayed in progress and to do

Here is an example how to filter the result by specific date range

By filtering this report for January, you can get a clear view of how long user stories took from "In Progress" to completion within that month.

If you’d like to explore this solution further, feel free to check out Timepiece on the Atlassian Marketplace or schedule a demo with us for a walkthrough of the app’s features. 🚀

Hope this helps!

Ayça

[Farid H_ Nia]

Thank you Ayca, I will review and try

[Madhu_RVS]

Hi @Farid H_ Nia 

Welcome to the community !!

You can use Jira Rest API's and build your own custom solution or if you are open to explore mktplace apps for detailed and flexible reports, take a look at

Time in Status Reports 

The "Status Transition Detail" and "Time in Status per time grain" reports will help you extract the information from the app. Do try it out

More details here

Disclaimer : I work for the vendor who built this app

[Petru Simion _Simitech Ltd__]

HI @Farid H_ Nia ,

If you have Script Runner, you can create a scripted field that will capture, from the ticket history, the date when the issue was transitioned into In Progress.

Then you use JQL to get all issues that have the desired value range for the scripted field you created.

Regards, 

Petru

[Hannes Obweger - JXL for Jira]

Hi @Farid H_ Nia

welcome to the community!

An issue's status changes are captured in an issue's issue history, which you can access from the issue view in Jira or via Jira's REST API. From these status changes, you should be able to find/calculate the values that you're looking for - however, it can be quite tricky to do that by hand.

That's the reason why a lot of users rely on solutions from the Atlassian Marketplace to do that for them. E.g., you may want to have a look at the app that my team and I are working on, JXL for Jira.

JXL is a full-fledged spreadsheet/table view for your issues that allows viewing, inline-editing, sorting, and filtering by all your issue fields, much like you’d do in e.g. Excel or Google Sheets. It also comes with a long list of so-called history columns that aren't natively available, including the date/time of transition to [status], time in [status], time between [status] and [status], and many, many more.

This is how it looks in action:

As you can see above, you can easily sort and filter by your history columns, and also use them across JXL's advanced features, such as support for (configurable) issue hierarchies, issue grouping by any issue field(s), sum-ups, or conditional formatting. Of course, you can also export your data to Excel or CSV in just two clicks.

Any questions just let me know,

Best,

Hannes