JQL Query to retrieve User Stories with Time spent on them, or subtasks

[Nicolas Mattenet]

Hi, 

I'm looking for a queries that can return the user stories on my backlog that are DONE with some criteria, and on whom time have been spend (either on the user story itself, or one of it subtask)

I have a query that works fine, as long as I don't have to look for timespent on subtasks : 
project in ("<MyProject>")
AND status in (Closed, Done, Built)
AND "Story Points" > 0
AND labels = label1
AND labels = label2
AND timespent > 0

But devs in my team also log time on subtasks, and sometimes not the user story itself.
I tried this :

project in ("<MyProject>")
AND status in (Closed, Done, Built)
AND "Story Points" > 0
AND labels = label1
AND labels = label2
AND ( timespent > 0 OR issueFunction in subtasksOf("timespent > 0"))

No JQL errors, but didn't work. I can't find any issue. Do you have an idea ?

Thank you very much

[Trudy Claspill]

I think this will work

project in ("<MyProject>")
AND status in (Closed, Done, Built)
AND "Story Points" > 0
AND labels = label1
AND labels = label2
AND issuetype not in sutaskIssueTypes()
AND (
  timespent > 0 OR (
    issueFunction in subtasksOf("project in ("<MyProject>")
      AND status in (Closed, Done, Built)
      AND "Story Points" > 0
      AND labels = label1
      AND labels = label2"
      AND timespent > 0))
    )
  )

Breaking it down, the first part will get all the non-subtasks that have story points and non-zero timespent:

project in ("<MyProject>")
AND status in (Closed, Done, Built)
AND "Story Points" > 0
AND labels = label1
AND labels = label2
AND issuetype not in subtaskIssueTypes()
AND (
  timespent > 0

The second part takes the non-subtask issues retrieved in the first part and selects just the ones that have non-zero timespent

AND (
  timespent > 0

So, at that point you have all the non-subtask issues with non-zero time spent.

The third part uses the first part (all non-subtask issues, whether they have timespent or not) and gets the subtasks of those issues, but just the subtasks that have non-zero timespent.

               OR ( 
  issueFunction in subtasksOf("project in ("<MyProject>")
    AND status in (Closed, Done, Built)
    AND "Story Points" > 0
    AND labels = label1
    AND labels = label2"
    AND timespent > 0))
  )