Find All Issues In a Project apart of future sprints or have no sprint value

[Patel_ Shivam]

Hi,

I am trying to essentially take this JQL query and put this into Analytics: 

- project = <Jira Project> AND type = Bug AND (Sprint IN futureSprints() OR Sprint = EMPTY) AND status NOT IN (Done, Cancelled)

I want to find all the bugs in a project that are apart of future sprints and not apart of any sprints. Here is the closest SQL query in analytics I have gotten to helping me:

SELECT
  I.issue_key,
  I.status AS ISSUE_STATUS,
  I.priority AS ISSUE_PRIORITY,
  S.name AS ISSUE_SPRINT,
  S.status AS SPRINT_STATUS,
  ISH.action,
  DATE_FORMAT(ISH.action_at,'yyyy-MM-dd HH:mm:ss') AS EVENT_TIME
FROM jsw_issue_sprint_history ISH
  INNER JOIN jira_issue I ON I.issue_id = ISH.issue_id
  INNER JOIN jsw_sprint S ON ISH.sprint_id = S.sprint_id
WHERE I.issue_type = 'Bug'
  AND {VAR_PROJECT.IN('I.project_id')}
  AND S.status IN ('future')
  AND I.status NOT IN ('Done', 'Cancelled')
ORDER BY
  I.issue_key, ISSUE_SPRINT, EVENT_TIME
LIMIT 1000000;

I have tried maybe 50 different ways to do this, and no luck. Currently, the query above shows me the correct tickets, but I am not able to get the tickets that do not have a sprint value. Also, for a ticket that is removed from a sprint and now does not have a value for it, it is still showing me the sprint that it was apart of (even though I thought this was checking for the most recent sprint value).

Can anyone help? Have been stuck on this for days and it is for a report I am trying to build. Any help is really appreciated.

Thanks,

Shivam Patel

[Valentina Blagoev]

Hey Patel,

Thank you for your question. I completely understand that sometimes finding the right query might be frustrating.

Have you tested adjusting the query the way below? 

AND (S.status IS NULL OR S.status = 'future') 

Have you also tried splitting the query into 2 separate ones?

If you have already tested these suggestions, and you weren't successful, please reach out to our support via the link below, so that we can further investigate:

https://support.atlassian.com/contact/#/

Regards,

Valentina