JQL Looking to filter out issues with past sprints, but no sprint currently assigned.

[Ben Cudars]

I have several tickets that are set up as below.

These are tickets that were in a sprint, then moved into the next sprint on sprint completion (as they were not completed during that sprint), before eventually being taken out of a sprint entirely and closed with no action.

I am trying to put together a JQL query that filters out these tickets that have 'Completed' sprints against them but no 'Current' sprint against them.

Is anyone able to assist?

[Ste Wright · Accepted solution]

Hi @Ben Cudars

Would a JQL such as this work:

project = ABC and Sprint in closedSprints() and Sprint not in futureSprints() and Sprint not in openSprints()

This locates:

• Issues which are in project ABC and are in a closed sprint but are not in...
• A future sprint or an...
• Open sprint

Ste

[Ben Cudars]

Afraid not, 

Apologies I should have provided my JQL earlier. For reference my current JQL is:

project in ("abc", "123") AND status changed to closed after 2019-06-30 AND Sprint IS NOT EMPTY

This returns all tickets closed after the 30/06 last year where the ticket had a sprint assigned at the time a sprint was completed or is in a current or future sprint. 

Changing my JQL to the below (as per your proposal) just excludes tickets in current or future sprints, it still returns those tickets that do not currently have a sprint but do have completed sprints as reported in my original description.

project in ("abc", "123") AND status changed to closed after 2019-06-30 AND Sprint IN closedSprints() AND Sprint NOT IN futureSprints() AND Sprint NOT IN openSprints()

[Ste Wright]

Hi @Ben Cudars 

I misunderstood your need - to be clear:

• You want to see all issues which were closed after 30/06/2019 and...
• Which had or have a sprint but not...
• Issues which had a sprint but were closed outside of a sprint (after being removed from a future sprint)

And there's no other factors which could be used here? EG if these were closed as not required, a resolution like "Won't Do"?

Ste

[Ben Cudars]

Apologies,

Yes that is correct. There are no other factors to consider in this case.

[Ste Wright]

Hi @Ben Cudars 

I'm not sure how possible this is in Cloud - there's no JQL function for "completed in sprint" which is what you need here.

I'd usually suggest searching via date range for a status change - it would depend if you're referencing a certain set of sprints or just generally any possible sprint.

So for example, if you're referencing all sprints before 30/06/2019 there might be something we could do with date ranges?

Ste

[Ben Cudars]

No worries Stephen, thanks for your help.

I can filter these tickets out in other, more involved ways (manually if I have to). Just thought I'd check in with the community to see if there was a way to do it more efficiently first :).

[Ste Wright]

No worries :)

I would suggest using resolutions in future as an option if these were undelivered work - so for example "Done" for resolved work but "Won't Do" or "Cancelled" for undelivered work.

Ste