Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in

JIRA JQL Query to find issues statusCategoryChangedDate after the due date

Yarisma Mendoza
I'm New Here
I'm New Here
Those new to the Atlassian Community have posted less than three times. Give them a warm welcome!
May 17, 2023

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?

4 answers

1 vote
Kate Kabir
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
June 15, 2023

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:

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

Kind Regards 

Kate

1 vote
mauricio.groth
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
May 19, 2023

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

1 vote
Walter Buggenhout
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
May 18, 2023

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
I'm New Here
I'm New Here
Those new to the Atlassian Community have posted less than three times. Give them a warm welcome!
June 14, 2023

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.

1 vote
Aziz Usman
I'm New Here
I'm New Here
Those new to the Atlassian Community have posted less than three times. Give them a warm welcome!
May 18, 2023

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.

Suggest an answer

Log in or Sign up to answer
DEPLOYMENT TYPE
CLOUD
PRODUCT PLAN
PREMIUM
TAGS
AUG Leaders

Atlassian Community Events