Why

It's a common need to visualize the issue keys from non-started sprints, also there is already a request to this in this ticket GHS-5773 - Getting issue details... STATUS . Many users have been asking about how to retrieve this data and the database query to generate this report is more complex than it looks, so here we are presenting the resolution (which was only tested in SQL servers). For other databases, the adaptation of the code will be required to match the database features and properties.

Resolution

1. At first, run the query below to identify the ID of the "Rank" custom field:

   SELECT * FROM customfield WHERE customfieldtypekey = 'com.pyxis.greenhopper.jira:gh-global-rank';

   Please remember the value of the "ID" column of this result;

2. Run this query to find the issues and its respective sprints:

   DECLARE @CUSTOM_FIELD_ID int = xxx;
    
   WITH list (position, id, issue, "next") as (
       SELECT
           0 AS position,
           rang.ID,
           rang.ISSUE_ID,
           rang.NEXT_ID
       FROM
           AO_60DB71_ISSUERANKING rang 
           LEFT OUTER JOIN AO_60DB71_ISSUERANKING rang2 ON (rang2.CUSTOM_FIELD_ID = rang.CUSTOM_FIELD_ID and rang2.NEXT_ID = rang.ISSUE_ID) 
       WHERE
           rang.CUSTOM_FIELD_ID = @CUSTOM_FIELD_ID AND
           rang2.ID is null
    
       UNION ALL
    
       SELECT
           position + 1,
           rang.ID,
           rang.ISSUE_ID,
           rang.NEXT_ID
       FROM
           AO_60DB71_ISSUERANKING rang
           JOIN list ON (rang.ISSUE_ID = list."next")
       WHERE
           CUSTOM_FIELD_ID = @CUSTOM_FIELD_ID
   ) 
   SELECT
       list.position,
       ji.pkey as "key",
       sm.MARKER_CONTEXT AS rapidView,
       sm.NAME AS sprint
   FROM
       list
       LEFT OUTER JOIN jiraissue ji ON ji.id = list.ISSUE
       LEFT OUTER JOIN AO_60DB71_SPRINTMARKER sm ON sm.ID = -issue
   ORDER BY
       position
   OPTION (maxrecursion 0);

   Where " int = xxx" (from the first line where we have the 'DECLARE' statement) will be the ID retrieved from the first query.

To understand the results gathered from the query

The result from the query above may seem a little bit confusing or hard to understand, but here is a quick explanation of it:

Here is one example of the results gathered from the query in a local JIRA database. You will see that the first 3 result's are not related to any sprint. This means that these issues ('TES-2', 'TES-3','TES-4') are from a sprint which has already been started, as you can see below (as there all of these issues are in Sprint 1).

After these 3 issues, you will see a null line (inserted to separate different sprint contents), then Issues 'TES-8' and 'TES-9' and right below in the sprint column you will see "Sprint 30". This means that 'TES-8' and 'TES-9' and from non-started sprint "Sprint 30".

The same will be for 'TES-5' and 'TES-6', which are from "Sprint 90" and 'TES-7' and 'TES-1' which are from "Sprint 60".

You can confirm the reliability of these results in the screenshots below: