Cycle time on given issues from Open to Completed state

[Johnson Anthony]

I have a JQL with me. Lets say "project = XXX ORDER BY ID desc" which returns some issues from it. Here for say, 15 issues pulled out from the JQL. Out of 15 issues 9 are in Completed state. I want the average time between the Open and completed state of those 9 issues alone. If issues may be reopened and complete. In that case, it should be not take the time between Reopened and In progress.  At the end the actual amount of time taken to complete an issue only taken into consideration and need the average cycle time from open to complete.  In what way we can achieve this. Any API available Or If we have any SQL queries are also fine to go with. 

[Tuncay Senturk]

Hi @Johnson Anthony 

Welcome to the community.

You can't have that data directly from either Jira or database without analyzing issues' change histories.

Enhancer Plugin for Jira has Time Between Statuses custom field on which you can configure to track time between Open till resolution excluding Reopened (if you want).

It has Average Time Between gadget in which you can get exactly the same report that you want. 

I am one of the folks behind Snapbytes, if you are on server/datacenter hosting and need help about this plugin you can reach out.

Regards

Tuncay

[Mariana_Pryshliak_Saasjet]

Hello @Johnson Anthony 

You can try Time in Status for Jira Cloud. Except for generating reports, this add-on allows getting Cycle and Lead Time by using the status grouping feature (adding the time of few statuses to one column). 

You can discover more about other tools for calculating Cycle and Lead Time in the "3 tools to analyze Cycle and Lead Time in the Jira" article.

If you have any additional questions, I'm here to help you)

Best regards, Mariana

[Mehmet A _Bloompeak_]

Hi @Johnson Anthony ,

As a free solution, you can try Status Time Free. It a limited but free version of Status Time app. Hope it helps.

[Emre Toptancı _OBSS_]

Hello @Johnson Anthony ,

Since you are asking for an SQL, I presume you are working on Jira Server or Data Center.

The changes on an issue (including status changes) are kept in ChangeItemGroup and ChangeItem DB tables. Unfortunately the data in the tables are raw records, more or less the same data you see in the History tab of your issue. I don't think it would be feasible to get the data you want with an SQL query.

You can get the same data via Jira REST API but once again, it gives raw records. You would need to do the calculation yourself.

As an alternative ready-built solution, our team at OBSS created Timepiece - Time in Status for Jira app for this exact need. It is available for Jira Cloud, Server and Data Center.

Time in Status allows you to see how much time each issue (wihch can be selected with a JQL filter) spent on each status or assigned to each assignee as well as entry/exit dates for all statuses. You can also combine statuses into consolidated columns to see metrics like Issue Age, Cycle Time or Lead Time.

You can get the app to calculate averages and sums of those durations grouped by issue fields you select. (For example see the average Issue Age per project and per issuetype.)

You can produce reports based on the default 24/7 calendar or define your own business calendars with custom workdays and working hours. (This one is important because a 24/7 calendar in most cases shows misleading data. For example an issue created at 16:00 on Friday and was resolved at 09:00 on next Monday seems to stay open for 2,5 days but in terms of business hours, it is only a few hours. You can see this using Time in Status by OBSS.)

All data can be exported as XLS, XLSX, CSV. The app introduces a REST API for accessing the calculated report output. All the report data can also be visualized by various chart types.

Timepiece - Time in Status for Jira

Emre Toptancı

OBSS