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

[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

[Mehmet A _Bloompeak_]

Hi @Farid H. Nia

Jira doesn't have a built-in solution for status transition dates. If you prefer using a marketplace app, you can try Status Time Reports app developed by our team. It mainly provides reports and gadgets based on how much time passed in each status.

Here is the online demo link, you can see it in action and try without installing the app.

To get a report of status transition dates, you can have a look at Status Count And Entry Dates report. Entry date(see In Development, Ready for Testing, In Testing, In Development columns.) is status transition date and status count(see #In Development, #Ready for Testing, #In Testing, #In Development columns) is how many times an issue is entered to this status.

App Features:

• This app has a dynamic status grouping feature so that you can generate various valuable reports as time in status, time in assignee, status entry dates and status counts, cycle time and lead time, resolution time, average/sum reports by any field(e.g. average in progress time by project, average cycle time by issue creation month).

• You can search issues by Project, Issue Type, Status, Assignee, Issue Creation/Resolution Date(and any other Date field) and JQL Query.
• Status durations are calculated according to the working calendar you define. Once you enter your working calendar into the app, it takes your working schedule into account too. That is, "In Progress" time of an issue opened on Friday at 5 PM and closed on Monday at 9 AM, will be a few hours rather than 3 days.
• You can set different duration formats.
• You can export reports in CSV file format and open them in MS Excel.
• You can also add this app as a gadget to your Jira dashboards and reach “Status Time” from Issue Detail page.
• You can enable/disable access to Status Time reports&gadgets and Issue Detail page per project, users, groups or project role.

For further details, you can have a look at Status Time Reports How to Videos.

If you are looking for a completely  free solution, you can try the limited version Status Time Reports Free.

If you have any questions, feel free to schedule a demo with us.

Hope it helps.

[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