JQL Not returning expected results

[John Viall]

I'm trying to find all the stories that are not currently "Done" and aren't in a current Sprint.

type =story AND Status != Done AND Project = XYZ AND Sprint not in openSprints()

This particular query, returns less items than expected. 41 to be exact.

However, if I look at the backlog in the project XYZ, and use a Quick Filter of type = Story, I come up with a number of 98.

To complicate things, I have two concurrent Sprints running within the same project, so trying variations of using the Sprint numbers, doesn't work either,

type =story AND Status != Done AND Project = Portal AND (Sprint != 63 OR Sprint !=64)

Returns 56 Records, including ones that are actually part of the Sprints 63 and 64.

I can't use Sprint is Empty, because some of these stories were part of a Sprint before, then pulled out. I have been hesitant to clear the old Sprints. I can do that, and it might make this easier, but afraid it will screw up the reporting on those older sprints??

Any suggestions would be greatly appreciated.

[Nic Brough -Adaptavist- · Accepted solution]

This sounds illogical, but it's there for a good reason.  Empty is not the same as not having a specific value.  It's a long explanation that I really want to do a proper write-up on, but the short story should get you going.

Try changing the last clause from

And Sprint not in openSprints()

to

And (Sprint not in openSprints() or Sprint is empty)

[Randy]

^ Nic posted the right answer.

As an aside, even though there's reasoning for it, Atlassian really should just include empty as part of the set returned from the != equality operators.  Checking for only empty should be moved to a different operator or function like isEmpty(Sprint) given the usage patterns between the two.

[John Viall]

Nic's Answer worked, but not intuitive, since some of the records contain data within the Sprint field, like 62. But since this works, I'm happy. Thanks everyone for the help.

[Randy]

@John Vialldo you mind marking "accept" on nic's answer? :)

[John Viall]

Saw the checkmark, and thought it had already been marked. Done, and thanks again.

[KellyW]

Hi John,

Does the following JQL match your criteria?

type =story AND Status != Done AND Project = Portal AND (Sprint not in (63, 64) or Sprint is empty)

Regards,
Kelly