How to find all tasks that were completed after the end date

[Абдуллаев Алишер]

I am trying to find all tasks that were completed after the end date (transitioned to the "Closed" status). My colleagues fill in start date and end date (custom fields) for their tasks.

I attempted to achieve this using JQL:

status = Closed AND CHANGED("Resolved") > "end date"
status = Closed AND "End Date" < CHANGED("Resolved")

However, without success! The results of the queries always indicate something like "end date is not a date" or similar issues. =( 

I've also tried to explore solutions using ScriptRunner, but it requires a significant amount of time to learn. =( 

If anyone knows how to solve this problem, please share! (I've already lost all hope.) PLZ HELP!

[Aswin Raj D · Accepted solution]

Dear @Абдуллаев Алишер ,

Here's an example JQL query using ScriptRunner's changed function that you can try: 

status = Closed AND issueFunction in dateCompare("", "End Date", ">", "Resolved")

 Please make sure the field names properly added in the JQL

[Абдуллаев Алишер]

Can you please explain a bit more (I am a complete beginner in ScriptRunner)

[Aswin Raj D]

Hi @Абдуллаев Алишер ,

Certainly! The JQL query I provided uses ScriptRunner's dateCompare function to find issues with a "Closed" status where the "End Date" custom field is greater than the date when the issue was resolved.

("", "End Date", ">", "Resolved"): These are the parameters for the dateCompare function.

• "": The first parameter is for specifying the date to compare against. In this case, it's left empty, meaning it will compare against the current date.

• "End Date": The second parameter is the name of the custom date field. Replace this with the actual name of your "End Date" custom field.

• ">": The third parameter is the operator, and we are using ">" to find issues where the "End Date" is greater than the resolved date.

• "Resolved": The fourth parameter is the field to compare against. In this case, it's the "Resolved" field, which is a standard field in Jira representing the date when an issue transitions to a resolved status.

Make sure to replace "End Date" with the actual name of your custom date field,

[Sean Chua _Adaptavist_]

Hey @Абдуллаев Алишер , apart from what has been shared from Aswin.

As a beginner with ScriptRunner, there is a few nice Getting Started Guides available to check out and top 10 commonly used JQL for your reading pleasure.

The JQL which is being shared is part of ScriptRunner's JQL Functions and you use "issueFunction" for its capabilities in your query. There are other Samples for "Date" available which you can use as well, and do check the available ScriptRunner JQL tutorial doc on how to work it better.

Hope you are able to do what you looking to do.

[Абдуллаев Алишер]

Hi again!

Dear @Aswin Raj D  Thank you very much!!! 

I also wanted to know what can be used to replace the first date here? which is blank in the examples above.

And also (x2), for some reason, the query displays tasks that were completed on the same day (end date = resolved date). Is it possible to configure this as well? 

[Aswin Raj D]

In the dateCompare function, the first parameter is used to specify a date to compare against. If you leave it blank (""), the function will compare against the current date. However, you can replace it with a specific date or another date field if needed.

For example, if you want to compare against a specific date, you can replace the first parameter like this:

 status = Closed AND issueFunction in dateCompare("2024-01-22", "End Date", ">", "Resolved")

Replace "2024-01-22" with the desired date in the format "YYYY-MM-DD."

To filter out tasks where the "End Date" is equal to the resolved date (completed on the same day), you can modify the query to use the >= operator instead of >:

 status = Closed AND issueFunction in dateCompare("", "End Date", ">=", "Resolved")

This change includes issues where the "End Date" is equal to the resolved date. Adjusting the operator to >= means "greater than or equal to."

So, the revised query looks for issues in the "Closed" status where the "End Date" is greater than or equal to the resolved date.

Feel free to adjust these queries based on your specific requirements and the behavior you want to achieve.

[Абдуллаев Алишер]

@Sean Chua _Adaptavist_ @Aswin Raj D - thanks in advance =)