Advanced JQL

[Teza Jonnalagadda]

Hello Team, Can one of you help on writing the advanced JQL for the below requirement, please?

In Project IT all the tickets that are not Resolved(tickets that are in Open and In-Progress) before 2021 plus ALL the tickets that are created in 2021?

Thanks.

[Jack Brickey · Accepted solution]

Try this...

project = IT and createddate >= startofyear() OR resolutiondate >= startofyear()

[Teza Jonnalagadda]

This is showing more data than needed, its pulling data for other projects as well.

[Jack Brickey]

Sorry left off some parentheses.

project = IT and (createddate >= startofyear() OR resolutiondate >= startofyear())

[Teza Jonnalagadda]

Thanks but this is still showing tickets that are created in 2018 and are in Closed state. Can you pls advise?

Here is my criteria, we only want to see the tickets that are created in 2021 and the ones that are open(not resolved) prior to 2021.

[Jack Brickey]

Do you mean by the ones that are open prior to 2021? Do you mean to say any issue that was created prior to 2021 that are still in an open state? Sorry I’m just not quite following what you’re looking for here

[Teza Jonnalagadda]

yes, all the tickets created in 2021 plus anything that is open from the time the project was created. Apologies for the confusion.

[Jack Brickey]

Maybe this then

project = IT and (createddate >= startofyear() OR statuscategory != done)

that JQL should give you all issues for project IT created this year regardless of the status and any issues created before 2021 that are not yet resolved. 

[Teza Jonnalagadda]

This worked like a charm! Thank you so much!

Could you also help me with another query, pls?

all the tickets created in 2021 plus anything that is open and created in 2020?

Thanks in advance.

[Teza Jonnalagadda]

@Jack BrickeyUpon looking closely, this is pulling the tickets that are fixed as well, is this because we are using too many resolutions and too many transitions? Is there a way to tailor this? pls. Thanks

[Jack Brickey]

Are the ones in the fixed status created in 2021 or prior? Above JQL returns everything in 2021 regardless of status. So if the fixed issues were created in 2021 and that would be consistent with the JQL. However if they were created prior to 2021 and that tells me that your fixed status does not equate to a done status category. 

[KAGITHALA BABU ANVESH]

Hi @Teza Jonnalagadda ,

For "all the tickets created in 2021 plus anything that is open and created in 2020?"

project = "IT" AND (createdDate >= startofyear('-1') and statuscategory != done) ORDER BY created ASC, updated DESC

Please check this.

Thanks,

Anvesh

[Teza Jonnalagadda]

@KAGITHALA BABU ANVESHThis is pulling tickets that are Completed as well(Resolution is Fixed). can you help?