JIRA JQL Query to find issues statusCategoryChangedDate after the due date

[Yarisma Mendoza]

I am writing a jql query to find issues where their status category was updated to done after the due date. I want to determine the number of issues that weren't completed by their due date.

I have tried doing this:

project in (ABC) AND statusCategory = Done AND statusCategoryChangedDate > due +1d

I am unable to use issueFunction. I am trying to find a workaround this. Can someone please help me?

[mauricio_groth]

Hi @Yarisma Mendoza 

I’m Maurício, a support engineer at Appfire and I’m here to help you.

Unfortunately, using JQL of Jira, you’ll not be able to do it dynamically.

In the app where my team works, JQL Search Extensions for Jira, you can use this query to find all issues that weren't completed by their due date:

 issue in dateCompare("duedate < done") AND project = ABC

Please contact our support if you have any other questions about this query.

We’ll be happy to help you!

Best regards,

Maurício

[Walter Buggenhout _ACA IT_]

Hi @Yarisma Mendoza and welcome to the Community!

When you say "you can't use issuefunction", are you aware why you can't use it? It is a part of marketplace app scriptrunner. It is worth checking if you have the app available on your site or not.

Comparing dates is not natively supported by JQL. While you could search like this:

project = ABC AND statusCategory = Done AND
statusCategoryChangedDate > startOfMonth()

you can't natively search against date fields on your issues. You will inevitably need to revert to a marketplace app for this or use automation to somehow label/mark issues that were overdue so you can easily search for them. Have a look at this related thread for a bit more background.

Hope this helps!

[Aziz Usman]

project = ABC AND statusCategory = Done AND statusCategoryChangedDate > due

This query assumes that the field name for that due date is "due" in Your Jira Instance. If the field name differs, please replace "Due" with the appropriate field name.

Remember to adjust the query based on your specific JIRA setup and field names.