How to Identify Issues that belongs to any Sprint in Jira Data Center

Summary

The purpose of this article is to show how to identify issues with Sprint and those that does not belong to any Sprint. This can be done via JQL and SQL.

Solution

1. All the issues that have been put into some sprint:

   1. JQL:

      1 Sprint is not EMPTY

   2. SQL:

      1 select ji.id, concat(P.pkey,concat('-',ji.issuenum)) from jiraissue ji join project P on P.id = ji.project join customfieldvalue cv on ji.id=cv.issue join customfield cf on cf.id=cv.customfield where cf.cfname='Sprint'

2. All the issues that have not been put into any sprint:

   1. JQL:

      1 Sprint is EMPTY

   2. SQL:

      1 select I.id,concat(P.pkey,concat('-',I.issuenum)) from jiraissue I join project P on P.id = I.project where I.id not in (select ji.id from jiraissue ji join customfieldvalue cv on ji.id=cv.issue join customfield cf on cf.id=cv.customfield where cf.cfname='Sprint')

3. Issues that belong to a specific sprint:

   1. JQL (if you provide the name here it will return issues for all sprints with the same name):

      1 Sprint = 'my sprint name'

   2. SQL query to list the Jira issues associated to the "my sprint name" sprint:

      1 2 3 4 5 6 7 8 9 select ji.id, concat(P.pkey,concat('-',ji.issuenum)) as issuekey, ads."NAME", ads."ID" from jiraissue ji join project P on P.id = ji.project join customfieldvalue cv on ji.id=cv.issue join customfield cf on cf.id=cv.customfield join "AO_60DB71_SPRINT" ads on cv.stringvalue=cast(ads."ID" as text) where cf.cfname='Sprint' and ads."NAME" = 'my sprint name' order by issuekey;