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?

[Kate Kabir]

Hi @Yarisma Mendoza 

I hope you are doing well.

In regard to the issueFunction and you are using ScriptRunner it is not possible with the standard JIRA functionalities but with ScriptRunner Cloud functions. 

If you are using the ScriptRunner Enhanced Search or Enhanced Search standalone product, it would be best to raise this at the support portal linked below so that we can assist your case better:

• ScriptRunner for Jira Cloud
• Enhanced Search standalone 

Sorry, I could not have much without more information for further investigation. 

Kind Regards 

Kate

[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]

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!

[Yarisma Mendoza]

Thank you for your help @Walter Buggenhout. 

I was able to add ScriptRunner Enhance Search and was not able to do the following to allow me to see the issues whose status category was changed to "Done" after the duedate: 

 issueFunction in dateCompare("project = ABC", "statusCategoryChangedDate > duedate") AND statusCategory = Done

I think it has to do with the date format that I am comparing to? Currently this is what works. 

project = ABC AND statusCategory = Done AND duedate = "2023/06/05" AND statusCategoryChangedDate > "2023/06/05 23:59"

 I have to look at the issues day by date to find out which issues were completed after the due date. I feel like there should be a faster way to do accomplish these results.

[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.