What's the issue?

[Dann Williams]

Hi all,

I'm currently trying to filter all tickets, in specific projects, by age and making sure they are not in any completed or "green" status'. However, whatever I try doesn't seem to work.

My query is currently:

project = SmartVan OR project = Shops OR project = "BE Configurator" OR Project = "Product Data" AND created >= 2018-01-01 AND created <= 2021-01-01 AND status not in (Green, Done, Cancelled, Complete) ORDER BY created ASC

Even with this, it's still pulling tickets that are in the green AND all date ranges.

What am I doing wrong?

Thanks

[Nic Brough -Adaptavist- · Accepted solution]

Welcome to the Community!

When you mix OR and AND in a logical query, most humans will misunderstand how the computers will read it, and it's quite likely you have constructed a query that does not do what you think it will.

In short, you need to use parenthesis to make your question clear. 

Jira reads queries left to right when there are no clarifying parentheses, so what your question comes out in plain English is: 

Show me a list that includes

• Issues in Smartvan
• Issues in Shops
• Issues in BE Configurator
• Issues in Product data created between two dates and not having one of four status

A swift correction would be

(project = SmartVan OR project = Shops OR project = "BE Configurator" OR Project = "Product Data") AND created >= 2018-01-01 AND created <= 2021-01-01 AND status not in (Green, Done, Cancelled, Complete) ORDER BY created ASC

But a slightly easier one would actually remove the need for the parentheses:

project in (SmartVan, Shops, "BE Configurator", "Product Data") AND created >= 2018-01-01 AND created <= 2021-01-01 AND status not in (Green, Done, Cancelled, Complete) ORDER BY created ASC

[Dann Williams]

Hi Nic,

Thanks for the explanation! Still new to the query side of Jira, but that makes more sense now!

Your code of...

project in (SmartVan, Shops, "BE Configurator", "Product Data") AND created >= 2018-01-01 AND created <= 2021-01-01 AND status not in (Green, Done, Cancelled, Complete) ORDER BY created ASC 

...worked great, so again, thanks for that! 

[Alexis Robert]

Hi @Dann Williams , 

you'll probably need to add parenthesis around your query to make sure the "AND" and "OR" statements are in the right order : 

(project = SmartVan OR project = Shops OR project = "BE Configurator" OR Project = "Product Data") AND (created >= 2018-01-01 AND created <= 2021-01-01 AND status not in (Green, Done, Cancelled, Complete)) ORDER BY created ASC

Also, instead of "status not in xxx" I would suggest "resolution is not empty" as this will ensure that you only get issues that are opened (issues with no resolution are not closed).

Let me know if this helps, 

--Alexis

[Dann Williams]

Hi Alexis,

Thanks for your response, it makes sense I needed more parenthesis!

Unfortunately, the "status not in<xxx>" was still drawing in Completed/Cancelled tickets, whereas I'm wanting to try and find tickets that are still historically backlogged/in progress/blocked - though, as I'm writing this I realise I could just search for those three criteria!

Thanks for the advice and help!

[Walter Buggenhout]

Hi @Dann Williams and welcome to the community,

That issue relates to the fact that you use multiple OR statements in your filter. When you do, you need to group your conditions using brackets, so Jira knows what criteria you want to combine. You can rephrase your query like this:

Project IN (SmartVan, Shops, "BE Configurator", "Product Data") AND
created >= 2018-01-01 AND created <= 2021-01-01
AND status not in (Green, Done, Cancelled, Complete) ORDER BY created ASC

Hope this helps!