nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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)

Accepted answers

Nic Brough -Adaptavist-

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/

Esteban Diaz

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

All comments

Nic Brough -Adaptavist-

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.

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.
Those date parameters look like what I need. Thanks for sharing those other helpfuls ones

Esteban Diaz

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

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

Thanks Esteban! I will try those out.

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).

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

sql

cloud

filter

jira-service-management-cloud

jira-service-management