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

It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

JQL to Find Tickets Where Status Changed during previous business day, supporting holidays

I am attempting to write a JQL query in order to filter tickets for a daily report.

There are 3 conditions which need to be met for the report as follows:

1) The results must include tickets which had their STATUS CHANGED to "Returned" during the previous business day

2) When the query is executed on a Monday, the expected results must include all tickets which had their STATUS CHANGED to "Returned" on Friday, Saturday, Sunday

3) Statutory Holidays are not considered business days, therefore, any tickets moved to "Returned" STATUS during a statutory holiday must appear in the results when the query is executed on the next business day

 

I've got the query started as follows:

status = Returned AND status CHANGED AFTER -1d OR ( status CHANGED DURING (-3d, endOfWeek(-1w) ) )

 

I believe this query will get take care of the first 2 conditions, however, I'm trying to figure out if there is any way to factor in the Statutory Holidays requirement. 

Is there an option to configure Stat Holidays in JIRA service desk which could then be leveraged in my JQL query?

1 answer

I would like to see this, too. I do not see a clear answer in the JQL reference, but there is something that is a possible useful lead; the "elapsed" function:

https://confluence.atlassian.com/jiracorecloud/advanced-searching-functions-reference-765593719.html#Advancedsearching-functionsreference-elapsedelapsed()

But this seems to require utilizing SLAs which is something my group has been avoiding so far, so I can't tell you if this would work. The documentation section "Creating SLA Calendars" on https://confluence.atlassian.com/servicedesk025/slas-754977501.html

does look pretty promising though, it describes configurable working hours in a day as well as working days and so on.

Please update if you have found a solution you like.

Suggest an answer

Log in or Sign up to answer
TAGS
Community showcase
Published in Jira Service Desk

The Complete Guide to Atlassian for ITSM

Hi Atlassian Community! This is Teresa from the Atlassian team. My colleague Paul Buffington @Buff and I are excited to share a brand new ITSM resource we’ve created – "The Complete Guide to At...

2,213 views 14 22
Read article

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you