Please can I get the SQL query or JQL which is fired when one clicks the transitions tab in the activity section?

Please can I get the SQL query or JQL which is fired when one clicks the transitions tab in the activity section?

Refer the image below. The objective is to get a report with this information for all issues in a particular sprint or time period.

This is for JIRA 6.2.x and above

Activity - Transitions Tab.jpg

2 answers

1 accepted

1 vote

It doesn't run any queries directly.  The issue contains a history which is a set of changes to the issue with date/time stamps.  When you look at the issue, JIRA pulls up all the data.  This tab simply reformats the history selectively.

The data for those changes is in the tables changegroup (holds issue id, and the change date and time) and changeitem (tells you what changed as part of a changegroup record, so it's a list of fields and their "from" and "to" values - you'll want to select this by looking for changes of "status")

A similar report can be created by using SQL for JIRA as it supports the issue changes history. Simply transform your JQL into a SQL query and join it with the ISSUECHANGES table which provides the old and new values for the field='Status' column filter as well as the user who performed the change and the timestamp.

And even if you use the SQL for JIRA Reports and Charts free extension you would be able to reproduce just the same GUI that you have attached as picture.

Suggest an answer

Log in or Join to answer
Community showcase
Sarah Schuster
Posted Jan 29, 2018 in Jira

What are common themes you've seen across successful & failed Jira Software implementations?

Hey everyone! My name is Sarah Schuster, and I'm a Customer Success Manager in Atlassian specializing in Jira Software Cloud. Over the next few weeks I will be posting discussion topics (8 total) to ...

3,314 views 14 20
Join discussion

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you
Atlassian Team Tour

Join us on the Team Tour

We're bringing product updates and pro tips on teamwork to ten cities around the world.

Save your spot