Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in

JQL for Active Tickets

Kurt De La Rosa August 9, 2023

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) 

2 answers

2 accepted

2 votes
Answer accepted
Esteban Diaz
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
August 9, 2023

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:

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

Cheers!


Esteban Diaz

Kurt De La Rosa August 9, 2023

Thanks Esteban! I will try those out.

2 votes
Answer accepted
Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
August 9, 2023

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 August 9, 2023

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-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
August 9, 2023

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 September 14, 2023

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 September 14, 2023

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 September 14, 2023

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.

Suggest an answer

Log in or Sign up to answer
DEPLOYMENT TYPE
CLOUD
PRODUCT PLAN
PREMIUM
TAGS
AUG Leaders

Atlassian Community Events