SQL Queries

Here you will find the list of the most used SQL queries!

Please pay attention to the

WARNINGS

and

INFO

✅ select ISSUES from…

This query allows you to create a table that includes issues according to your JQL query

Please pay attention that SQL do not allowed execute all issues from Jira at once. So you need to put Issue ID or JQL query, that selects issues from a particular place (project, user, sprint, board etc)

PROJECTNAME* - please note that in JQLs you need to use real names of your Projects, Users, Sprints ,etc.

DESCRIPTION

QUERY

Select all Issues from a particular project

select *
from ISSUE i
where JQL='project=PROJECTNAME'

Select all Issues of a current user

select *
from ISSUE i
Where JQL='reporter=currentUser()'

Select all Issues from an open sprints

select *
from ISSUE i
where JQL='Sprint in opensprints()'

✅ convert ISSUE STATUS into a text format

This query allows you to convert issue status ID into text format

DESCRIPTION

QUERY

Select Issues and statuses in text format from a particular project

select `KEY` as `Issue`, ws.NAME as `Status`
from ISSUE i
join WORKFLOWSTATUS ws on i.STATUSID=ws.ID
where i.JQL= 'project = PROJECTNAME'

PROJECTNAME* - please note that in JQLs you need to use real names of your Projects, Users, Sprints ,etc.

✅ format COMMENTS as ordinary text

Query converts data from column COMMENTS from HTML to a text format

DESCRIPTION

QUERY

Select Issues with comments ( text format) from a particular project

select i.`KEY` as `Issue`, FORMATHTML(ic.BODY) as `Comment`
from ISSUE i
join ISSUECOMMENT ic on ic.ISSUEID = i.ID
where i.JQL = 'project = PROJECTNAME'

PROJECTNAME* - please note that in JQLs you need to use real names of your Projects, Users, Sprints ,etc.

✅ convert UserID into a text format

DESCRIPTION

QUERY

Convert UserID into a text format

(Assignee, Reporter)

select LINKISSUE(i.`KEY`) as `ISSUE`, x.DISPLAYNAME as `Assignee`, y.DISPLAYNAME as `Reporter`
from ISSUE i
join USER x on i.ASSIGNEEID=x.ACCOUNTID
join USER y on i.REPORTERID=y.ACCOUNTID
where i.JQL='project=PROJECTNAME'

PROJECTNAME* - please note that in JQLs you need to use real names of your Projects, Users, Sprints ,etc.

👥 Sprint team members

Name	Role

✏ Sprint planning meeting items

Use this template to structure your meeting, set expectations and goals, and define the backlog for the upcoming sprint. For detailed instructions and best practices, see our sprint planning guide and review how to estimate story points.

Agenda

Previous sprint summary

Sprint theme
Story points
Summary

Details

Start date
End date
Sprint theme

Velocity tracking

Adjusted velocity tracking

Capacity planning

You can customize this template to change or add capacity measurements. You can also review older sprints by adding columns.

	Current sprint	Previous sprint
Total days
Team capacity
Projected capacity
Individual capacity

✅ select ISSUES from…

✅ convert ISSUE STATUS into a text format

✅ format COMMENTS as ordinary text

✅ convert UserID into a text format

👥 Sprint team members

✏ Sprint planning meeting items

Agenda

Previous sprint summary

Details

Velocity tracking

Adjusted velocity tracking

Capacity planning

Potential risks

?? Sprint planning resources

Sprint boards and retrospectives

Team resources and definitions