JQL for Active Tickets

[Kurt De La Rosa]

I am trying to create a JQL formula that will show me all of the "active" tickets within a date range that my team is working. As of now, I was using "created" as the driver for the formula but realize that this does not include tickets that were created prior to the date range but the team is actively working. Has anyone created a SQL formula like this? If so, would you mind sharing the language you used for:

• Showing all "active" tickets; i.e. anything that was created and is not marked as "done" as well as any ticket created
• Setting a date range that can be adjusted/edit for monthly reviews (example: June 1 to June 30) 

[Esteban Diaz · Accepted solution]

Hi @Kurt De La Rosa !

Maybe a JQL like this one could help you? 

project = x and status was Open DURING( "2023/8/1", "2023/8/31") and status =! Done

You can try other operators like:

• WAS
• WAS IN
• WAS NOT IN
• WAS NOT

I hope this has been useful to you.
I will be waiting for your comments. 😉

Cheers!

Esteban Diaz

[Kurt De La Rosa]

Thanks Esteban! I will try those out.

[Nic Brough -Adaptavist- · Accepted solution]

Welcome to the Atlassian Community!

I need to make some assumptions here:

• You mean "JQL", not "SQL", as you can't use SQL on Cloud systems.  (Also it would be an utter nightmare if you were on Server/DC and actually reading the Jira database - it's simply not usable for reporting.)
• You do not mean "anything that was created and is not marked done as well as any ticket created", because that translates to "all issues".  I assume you mean "all not done issue"
• I do not know how you are marking things as done, so I'll work with a simple definition

The answers are

• Status != Done
• created >= "2023-6-1" and created < "2023-6-30"

But you can be more clever with the dates:

• created >= -40d and created <= -10d
  • will return issues created between 40 and 10 days back from the day you run the filter
• created >= startOfMonth()
  • will return issues created after the beginning of the current month
• created >= startOfMonth(-1)
  • will return issues created after the beginning of last month

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

[Kurt De La Rosa]

Hey @Nic Brough -Adaptavist-. thanks for the quick response! You are absolutely correct; I meant JQL not SQL.

1. I think the != function may work best. However, do you know of a function or formula that translates to "all tickets that have had a status change"; i.e moved from "open" to "in development" kind of thing.
2. Those date parameters look like what I need. Thanks for sharing those other helpfuls ones

[Nic Brough -Adaptavist-]

The "changed" operator might be useful, but it is very limited - it only works on a few fields

For example : ... and (status changed from to-do to done)...  could pull out stuff that bypassed all development.

The one I nick from the docs is a bit more real-life:  priority CHANGED BY freddo BEFORE endOfWeek() AFTER startOfWeek()

[Kurt De La Rosa]

Hey Nic,

Jumping back into this thread. I am trying to look at all tickets during a specified date range. Meaning, ticket statuses can equal any of the following (open, scoping, development, in review, deployment, done, declined).

[Kurt De La Rosa]

I am using... and status changed during (date,date)

I get results but my concern is that I am missing tickets that did not have a status change during that period and remained in one of the statuses listed above.

[Kurt De La Rosa]

I think we found a solution....

 AND "created">="dateX" and "created"<="dateY" AND "updated">="dateX" and "updated"<="dateY"

This shows us anything created and updated within the specified time range.