Creating a JIRA JQL query to find issues with status changes after a specific date field

Other Questions Jira
1

I need help with a JIRA JQL query. I want to find issues where the status changed to a certain value after the date in a specific field. Here’s what I’ve tried:

due is not EMPTY and status changed to "TargetStatus" after due

But this doesn’t work. JIRA says it needs a text date or period. I get an error about invalid date formats.

I can check for current overdue items like this:

status not in ("TargetStatus","Finished") and due < now()

But that’s not exactly what I need. I want to find all overdue items, including those that moved to “TargetStatus” or were finished after their due date.

Does anyone know how to do this? Or something close to it? I’m stuck and could use some advice. Thanks!

2

I’ve wrestled with similar JIRA queries before, and I think I might have a solution for you. Have you considered using the ‘statusChangeDate’ function? It’s pretty handy for these kinds of scenarios. Try something like this:

statusChangeDate(‘TargetStatus’) > due OR statusChangeDate(‘Finished’) > due

This query should catch all issues where the status changed to either ‘TargetStatus’ or ‘Finished’ after the due date. It’s a bit more precise than just looking at general changes.

If you need to narrow it down further, you could add more conditions. For example:

(statusChangeDate(‘TargetStatus’) > due OR statusChangeDate(‘Finished’) > due) AND status in (‘TargetStatus’, ‘Finished’)

This ensures you’re only getting issues currently in those statuses. Hope this helps you out!

3

I’ve encountered a similar challenge before and found that using the WAS operator in JIRA can help capture transitions relative to a date field. You might try using a query like:

status WAS NOT ‘TargetStatus’ BEFORE ‘due’ AND status = ‘TargetStatus’

This approach checks that an issue was not in the targeted status before the due date and then moved into it. For finished items, you can extend the logic as follows:

(status WAS NOT ‘TargetStatus’ BEFORE ‘due’ AND status = ‘TargetStatus’) OR (status WAS NOT ‘Finished’ BEFORE ‘due’ AND status = ‘Finished’)

This should capture cases where the status changed after the due date. Experiment with these queries to see which one best fits your needs.

4

hey Isaac, I’ve dealt with similar jql headaches. Have u tried using the ‘changed’ function? something like this might work:

changed > due AND status in (‘TargetStatus’, ‘Finished’)

This should catch issues that changed after the due date and ended up in those statuses. lmk if that helps!