Validate JQL query to capture status that remain "To Do" over a period of time

[GeeLong]

Wanted to validate script to see if I am on the right track and looking for feedback to see whether this can be refined further.

- I want to filter out Issues that have been in "To Do" up till today OR

- Capture issues that has changed from "To Do" to "In Progress" up to today OR 

- Capture issues that has changed from "To Do" to "Done" up to today

Below is JQL written: 

Project = "Project Name" AND statusCategory IN ("To Do") AND NOT Status Changed AFTER "2023-09-27" OR (project = "Project Name" AND Status Changed FROM "To Do" to "In Progress" DURING ("2023-09-01", "2023-09-27")) OR (project = "Project Name" AND Status Changed FROM "To Do" to "Done" DURING ("2023-09-01", "2023-09-27"))

[Trudy Claspill · Accepted solution]

Hello @GeeLong 

Welcome to the Atlassian community.

Your second and third bullet items are correctly defined in the second and third OR clauses in your JQL statement.

I'm not sure that I understand the goal of your first bullet item. Can you explain that one in more detail? The JQL that you have written for that will get the issues that

- currently are in a status that is in the "To Do" status category and have been in that status unchanged since 2023-09-27

That does not necessarily exclude issues that have been in a status of "To Do" for some time prior to today, which is what I think your first bullet states.

[GeeLong]

Thank you @Trudy Claspill 

With the first clause, I am hoping to filter on issues that have in "To Do" status where they have not changed/moved up till most current date. This came about due to a request to identify the number of stories/ or issues sitting in the backlog.  

[Trudy Claspill]

Do you want to include those issues or exclude them from your results set?

If you want the issues that have been in the "To Do" status and have never changed to another status as of the start of today, then the JQL you need is

Project = "Project Name" AND status IN ("To Do") and NOT status CHANGED BEFORE startOfDay()

[GeeLong]

Yes I would like to include those issues where "To status" never changed.

Thanks for the suggestion.

An extension to the JQL script written, what will be the most optimal way of writing it so I don't have to keep adjusting the dates if I am starting to look at it month on month?

[Trudy Claspill]

If you want to dynamically derive the dates each time you execute the filter, look at the incorporating the startOf and endOf functions instead of hard-coded dates.

You can find more information on those functions here:

https://support.atlassian.com/jira-software-cloud/docs/jql-functions/