Partners

SQL for Jira Cloud is the powerful tool, particularly when used by skilled professionals who understand the intricacies of both Jira and SQL. As a Jira Consultant or implementation partner you can leverage SQL for Jira Cloud to provide your clients with deep insights previously not accessible or requiring extensive data integrations.

Let’s dive into a couple examples.

Changelogs

Each change to Jira issue, be it status change or a change in the field value, is recorded in the changelog. This makes it a very powerful source of information for data analysis yet not available from JQL.

With SQL for Jira Cloud you can easily query the changelog table to get the history of changes for any issue. This can be used to create reports, track changes, or even to create custom fields based on the history of changes.

Let’s say we want to see all status changes for a specific issue. We can use the following query:

select issues.key,from_value, to_value from changelog
INNER JOIN issues ON issues.id=changelog.issue_id
WHERE field_id='status'

But let’s say we want to understand how many issues where reopened during last 3 months. We will consider issue reopened if its status has been changed to ‘In Progress’ at least twice. It’s somewhat complex SQL query to craft. Exactly for those cases we have AI assisted queries allowing you to explain data request in the “human” language and let AI convert it into SQL for you.

For example let ask AI what we want to get:

return me list of issues that have been in status 'In Progress' at least twice during last 3 months. Show number of times each issue has been in In progress status as well.

As you see from the screenshot, AI created a SQL query with data aggregation and executed it for us returning issue IDs and number of times they have been in ‘In Progress’ status.

Now let’s ask it to return issue key instead of ID:

return me list of issues that have been in status 'In Progress' at least twice during last 3 months. For each issue show issue key and number of times it has been in In progress status.

This time AI figured it needs to JOIN issues table to get issue key and returned the result we wanted.

The same way as queries statues changes we can work with any other field in Jira, including custom fields, which in combination with powerful filtering and aggregation possibilities of SQL can provide you with deep insights into Jira data.

Project level analysis

Another example of how SQL for Jira Cloud can be used to create an overview across all projects. Let’s say we want how large is backlog of each project in one overview. Let’s ask AI to create a query for us:

show me for each project how many issues are in "To Do". Output project name and number of issues.

Now let’s say we want to see more elaborate status, specifically: backlog, in progress and done issues. Let’s ask AI again:

show me for each project how many issues are in "To Do", "In progress" and "Done " statuses. Output project name and number of issues is each status.

What we didn’t tell AI is that in some projects “Done” status is called “Launched”, so let’s go and update the query it generated to include “Launched” status as well:

SELECT projects.name AS project_name, 
       SUM(CASE WHEN issues.status = 'To Do' THEN 1 ELSE 0 END) AS todo_count, 
       SUM(CASE WHEN issues.status = 'In progress' THEN 1 ELSE 0 END) AS in_progress_count, 
       SUM(CASE WHEN issues.status = 'Done' OR issues.status='Launched' THEN 1 ELSE 0 END) AS done_count 
FROM projects 
LEFT JOIN issues ON projects.id = issues.project_id 
GROUP BY projects.name;

As you can see it now picked up “Launched” status in project “Go to market sample” and counted it as “Done”.

To summarise

There is much more you can do with SQL for Jira Cloud. It’s a powerful tool that can be used to create custom reports, track changes, dive deeper or create an overview.

Especially in the hands of skilled professionals who understand both Jira and SQL, it can be a game changer for your clients.

If you are a Jira consultant or implementation partner, you can leverage SQL for Jira Cloud to provide your clients with deep insights previously not accessible or requiring extensive data integrations.

We have a free trial available, please check it out on our Atlassian Marketplace listing: SQL for Jira Cloud

Or feel free to reach out if you’d like to get more information via our Contact Form, would be glad to get in touch!