JIRA Query help!

[Vijay Kumar]

I am trying to execute below JIRA query to fetch the issues like User story, Defect, Task & Subtasks underneath a defect, task and story are also not in completed status. Basically, it should show all the stories, defects & tasks, which meets following criteria,

1) Story/Defect/Task is in 'OTHER THAN COMPLETED' status but subtask/s are 'COMPLETED' status

2) Story/Defect/Task is in 'COMPLETED' status, but subtask/s are in 'OTHER THAN COMPLETED' status

3) Story/Defect/Task is in 'OTHER THAN COMPLETED' but subtask/s are also 'OTHER THAN COMPLETED' status

project = ABC  AND "Assigned Team" = "XYZ" AND issuetype in (Story, Defect, Task) AND status != "Completed" AND sprint in openSprints() AND issueFunction in subtasksOf("status != Completed")

I did execute above query but giving me zero results. I cross-verified against JIRA board, most of my stories and subtasks are in To-Do, in progress statuses as sprint started 3 days ago only.

[David Nickell · Accepted solution]

I don't do scriptrunner - but how can something be both an issueType in (Story, Defect, Task)  AND subtask of anything?  I think you mean OR.... you re look for non completed tasks as well as non complete subtasks (I think).  That is techically an "OR".  Be sure to use proper parenthesis as well .    Myabe like this?:

project = ABC  AND  (   "Assigned Team" = "XYZ" AND issuetype in (Story, Defect, Task) AND status != "Completed" AND sprint in openSprints()  )  OR issueFunction in subtasksOf("status != Completed")

Good luck

[Vijay Kumar]

Thank you @David Nickell for the reply. My apologies for the late reply.
Yes..i tried the query you suggested. When User story and all the subtasks underneath that story are in progress, it was able to filter those issues. But when I tested the scenario, where user story is in 'Completed' status, but one of the subtasks (out of 5) is in 'In-Progress' status. This time it couldn't show that as part of the query results. 
So alternatively, I tried below query, but unfortunately it didn't help.

project = ABC AND ("Assigned Team" = "XYZ" AND issuetype in (Story, Defect, Task) AND status != "Completed" OR issueFunction in subtasksOf("status != Completed")) AND sprint in openSprints()

Any other suggestions?

[Vijay Kumar]

@David Nickell 

I am trying to find issues as per the below conditions & their individual queries as follows & those queries work absolutely fine. They are time tested.

1) Story/Defect/Task is in 'COMPLETED' status, but subtask/s are in 'OTHER THAN COMPLETED' status.
project = CDP AND "Assigned Team" = "Chimera - Core API Team" AND issuetype in (Story, Defect, Task) AND status = "Completed" OR issueFunction in subtasksOf("status != Completed") AND sprint in openSprints()

2) Story/Defect/Task is in 'OTHER THAN COMPLETED' status but subtask/s are 'COMPLETED' status.
project = CDP AND "Assigned Team" = "Chimera - Core API Team" AND issuetype in (Story, Defect, Task) AND status != "Completed" OR issueFunction in subtasksOf("status = Completed") AND sprint in openSprints()

3) Story/Defect/Task is in 'OTHER THAN COMPLETED' but subtask/s are also 'OTHER THAN COMPLETED' status.
project = CDP AND "Assigned Team" = "Chimera - Core API Team" AND issuetype in (Story, Defect, Task) AND status != "Completed" OR issueFunction in subtasksOf("status != Completed") AND sprint in openSprints()

Now I want to put all of these in one query as i need to find issues which meets all these criteria. Hence, I am writing below queries. None of it worked properly,

THE QUERIES WHICH DIDN"T WORK:

1) project = CDP AND "Assigned Team" = "Chimera - Core API Team" AND issuetype in (Story, Defect, Task) AND sprint in openSprints() AND ( (status = "Completed" OR issueFunction in subtasksOf("status != Completed")) OR (status != "Completed" OR issueFunction in subtasksOf("status = Completed")) OR (status != "Completed" OR issueFunction in subtasksOf("status != Completed")) )

2) project = CDP AND "Assigned Team" = "Chimera - Core API Team" AND issuetype in (Story, Defect, Task) AND sprint in openSprints() AND ( (status = "Completed" OR issueFunction in subtasksOf("status != Completed")) AND (status != "Completed" OR issueFunction in subtasksOf("status = Completed")) AND (status != "Completed" OR issueFunction in subtasksOf("status != Completed")) )

3) project = CDP AND "Assigned Team" = "Chimera - Core API Team" AND issuetype in (Story, Defect, Task) AND sprint in openSprints() AND ( (status = "Completed" OR issueFunction in subtasksOf("status != Completed")) OR (status != "Completed" OR issueFunction in subtasksOf("status = Completed")) OR (status != "Completed" OR issueFunction in subtasksOf("status != Completed")) )

4) project = CDP AND "Assigned Team" = "Chimera - Core API Team" AND issuetype in (Story, Defect, Task) AND ( (status = "Completed" OR issueFunction in subtasksOf("status != Completed")) OR (status != "Completed" OR issueFunction in subtasksOf("status = Completed")) OR (status != "Completed" OR issueFunction in subtasksOf("status != Completed")) ) AND sprint in openSprints()

[David Nickell · Accepted solution]

Way to stay on task!  So, it should be as simple as combining the 3 working queries with "OR"

(project = CDP AND "Assigned Team" = "Chimera - Core API Team" AND issuetype in (Story, Defect, Task) AND status = "Completed" OR issueFunction in subtasksOf("status != Completed") AND sprint in openSprints())

OR

(project = CDP AND "Assigned Team" = "Chimera - Core API Team" AND issuetype in (Story, Defect, Task) AND status != "Completed" OR issueFunction in subtasksOf("status = Completed") AND sprint in openSprints())

OR

(project = CDP AND "Assigned Team" = "Chimera - Core API Team" AND issuetype in (Story, Defect, Task) AND status != "Completed" OR issueFunction in subtasksOf("status != Completed") AND sprint in openSprints())

---

Ugly - right?  Lets see how ChatGPT reduces it now >>

---

ChatGPT suggestion 1:

project = CDP AND
"Assigned Team" = "Chimera - Core API Team" AND
issuetype in (Story, Defect, Task) AND
sprint in openSprints() AND
(
(status = "Completed" OR issueFunction in subtasksOf("status != Completed"))
OR
(status != "Completed" OR issueFunction in subtasksOf("status = Completed"))
OR
(status != "Completed" OR issueFunction in subtasksOf("status != Completed"))
)

ChatGPT suggestion 2:

project = CDP AND
"Assigned Team" = "Chimera - Core API Team" AND
issuetype in (Story, Defect, Task) AND
sprint in openSprints() AND
(
status in ("Completed", "Not Completed") OR
issueFunction in subtasksOf("status in ('Completed', 'Not Completed')")
)

Let us know what works :-) 

[Vijay Kumar]

@David Nickell  MS AI Copilot was merely a help in this case..lol :(
Lemme try ChatGPT one and let you know.

[Vijay Kumar]

@David Nickell  Sorry again. The first query couldn't exclude the invalid records, and second query threw following error,
"Error in scripted function: subtasksOf, see below"

[David Nickell]

what about the version (Old School -- aka me) that just combined your working queries?  You could also save each of the three as separate filters and then use the filter IDs in JQL

[Vijay Kumar]

@David Nickell i am not sure about this approach. Could you please elaborate?