JQL for tickets that, at any time, went past the Due Date when not in Closed status?

[Graham Wellington]

Is there a way to query tickets that, at any time, went past their due date when not in Closed status?

Note that I am interested in querying tickets that ever went past their Due Date, not just tickets that are currently past their Due Date.

For example:

Ticket is created on January 1, 2023 and the Due Date field is set to January 15th, 2023.

The ticket sits open for a month with no activity.  On February 1, 2023, the Due Date field is updated to February 28th, 2023 and remains open.

On February 10th, 2023, I run a query and see the ticket with a Due Date of February 28th, 2023.  The ticket does not appear past due but at one point, it was past due.  I would like to be able to track how often this is happening.

Is there a way to query for these situations?

Thanks, Graham

[Bill Sheboy · Accepted solution]

Hi @Graham Wellington -- Welcome to the Atlassian Community!

I do not believe that is possible with built-in JQL features; it would require comparing two issue fields in the JQL: the Due Date and the Status Category Change Date (assuming your "Closed" status has a category of "Done").

Your options to solve this may depend on how often you need this:

• need this one time
  • export your issues to a spreadsheet and compare the dates from there
• need this often, and have money to spend
  • investigate marketplace addons for JQL which support such queries
• need this often, and do not have money to spend, but are willing to learn and build something
  • using the REST API, build something to look at issue changelogs to determine the due date when issues moved to Closed, compare them, and then report as needed
  • you could also do this with automation rules, for smaller batches of issues

Kind regards,
Bill

[Graham Wellington]

Got it, thanks @Bill Sheboy