How can I find epics that had sub tasks that were resolved AFTER the epic was resolved?

Question

I am using the following query to find all epics that have sub-tasks that match a certian criteria:

issueFunction IN epicsOf("issueFunction IN parentsOf('...')")

But what I need to do is find epics that are resolved and had sub-tasks that were resolved AFTER the epic was resolved.

Matt Parks · Answer

I had something that just needed a little tweaking, but this seems to work in my environment:

import com.atlassian.jira.component.ComponentAccessor
import com.atlassian.jira.user.ApplicationUser
import com.atlassian.jira.jql.parser.JqlQueryParser
import com.atlassian.jira.issue.Issue
import groovy.transform.Field
import com.atlassian.jira.bc.issue.search.SearchService
import com.atlassian.jira.web.bean.PagerFilter

@field SearchService searchService = ComponentAccessor.getComponent(SearchService.class)

ApplicationUser user = ComponentAccessor.getJiraAuthenticationContext().getLoggedInUser()
Date latestResolved = null

def issues = getTemplateEpicIssues(issue, user)

issues.each
{
    def subtaskList = it.getSubTaskObjects()

subtaskList.each
    {
        if (latestResolved == null || it.getResolutionDate() > latestResolved)
        {
            latestResolved = it.getResolutionDate()
        }
    } 
}

return latestResolved

Collection<Issue> getTemplateEpicIssues(Issue issue, ApplicationUser user)
{
    def jqlQueryParser = ComponentAccessor.getComponent(JqlQueryParser)
    def childrenQuery = jqlQueryParser.parseQuery(""" issuefunction in issuesInEpics("key = ${issue.key}")""")
    def children = searchService.search(user, childrenQuery, PagerFilter.getUnlimitedFilter())
    log.error("Found Children: " + children.getEnd())

Matt Parks · Answer

You'd have to use the dateCompare() issuefunction, but that only works when comparing dates from the same issue. To my knowledge, there is no way to compare dates from multiple issues.

However, you could create a scripted field on the epic that stores the most recent resolved date among all sub-tasks under the epic. Then run the following JQL:

issuefunction in dateCompare("issuetype = epic", "resolved < scriptedFieldName")

If you need assistance with writing the scripted field, I think I have written something that does something similar, although it might take me a bit to modify it to this particular use case.

To confirm, I've never tried to run JQL against a scripted field, but I think it would work.