Exclude weekends in JQL Query for Service level agreements (Jira Software , Jira Cloud)

Dipak Naokar June 17, 2022

Here is my description.

We have a service level agreement (SLA) with our customer facing team. It says once the bug is created, within 3 business days, the status should move to  at least "IN-DEV" (closed is not possible as the Queue of tickets is long).

So we have created a custom field called as "SLA breach". This field should have "YES"  value if the duration between "NEW" and "IN-DEV" is longer than 3 business days (but exclude weekends).

Example:

  • If the new bug is created on Monday (then), it should go to "IN-DEV" before Wed (EoD), else on Thursday - it should be SLA breach.
  • If the new bug is created on Tuesday (then), it should go to "IN-DEV" before Thu (EoD), else on Friday - it should be SLA breach.
  • If the new bug is created on Wednesday (then), it should go to "IN-DEV" before Fri (EoD), else on Saturday - it should be SLA breach.
  • If the new bug is created on Thursday  (then), it should go to "IN-DEV" before next Mon (EoD), else on Tuesday - it should be SLA breach.
  • If the new bug is created on Friday  (then), it should go to "IN-DEV" before  next Tue (EoD), else on Wednesday - it should be SLA breach.
  • If the new bug is created on Saturday  (then), it should go to "IN-DEV" before  next Wed (EoD), else on Thursday - it should be SLA breach.
  • If the new bug is created on Sunday  (then), it should go to "IN-DEV" before  next Wed (EoD), else on Thursday - it should be SLA breach.

I know the Automation and I have created the cron jobs to run every night.

My problem is -3d is not excluding the weekend. (So A bug created on Friday, is flagged as SLA breach on Monday itself as Sat and Sun are counted by system)

Can you please suggest me a JQL filter to have the SLA breach for above conditions that will exclude weekends from the counting of "3 days"?

In other words I just want to count business days.

4 answers

0 votes
Nallareddy Manasa August 23, 2022

@Dipak Naokar 

 

You can do one thing..

1. create  a custom field(Select List (multiple )) with the name as "creation day" .(options would be Monday to sunday). Make sure to keep its default value as NONE

2. then create one more custom field  similar to "creation day"  now name it as "Today" "Today". Make sure to keep its default value as NONE

3.Now write an automation rule as below instead Resolved day you need to select "creation day".

AR.JPG

4.  for "Today" custom field as well , 

AR2.JPG

5,. This will help you to fill both the fields.

6. Now write the major rule , as below

Take trigger as Scheduled and write JQL query as below 

project = xx AND issuetype = xx AND status=NEW AND ((creation day = Monday AND Today = Thursday ) OR ( creation day = Tuesday AND Today = Friday ) OR ( creation day = Wednesday AND Today = Monday ) OR ( creation day = Thursday AND Today = Tuesday ) OR ( creation day = Friday AND Today = Wednesday )( creation day = Saturday AND Today = Thursday )( creation day = Friday AND Today = Thursday ))

7. And select New action as Edit issue fields , under this  select "SLA breach" customfiled and select value as yes.

Hope this is what your expecting output.

Please let me know if you have any quires.

Dipak Naokar August 23, 2022

@Nallareddy Manasa Thank you.

I will need to try this.

But it is asking to create more custom fields. I will use the sandbox for the same. 

Is your problem on the other thread has been solved?

Nallareddy Manasa August 23, 2022

Yes my problem got resolved with above solution .!

In order to exclude weekends, two custom fields must be added. With the help of those two fields and an automation rule, I achieved the desired result  .

0 votes
Olha Onofriichuk_SaaSJet
Marketplace Partner
Marketplace Partners provide apps and integrations available on the Atlassian Marketplace that extend the power of Atlassian products.
June 24, 2022

Hi, @Dipak Naokar 

I’ll try to help you. If I understand your problem correctly, it may be solved via plugins for Jira, for example, SLA Time and Report for Jira add-on. Here, if you want to count only business days and exclude weekends from the counting of "3 days", you just need to create a custom field called “SLA breach”. And this field should have "YES"value if the duration between "NEW" and "IN-DEV" is longer than 3 business days (but exclude weekends). This will perfectly work in SLA.

Here is a screenshot with possible conditions:

new in-dev.png

I hope this is what you need.

Also, this add-on is created by my SaaSJet team, so if you have any questions, I’m here to answer them.

Dipak Naokar June 26, 2022

@Olha Onofriichuk_SaaSJet 

Thanks for suggesting this app.

I have seen the details and it looks like a proper solution to the problem that I have raised.

But, the budget is a constraint hence we cannot go for the paid plug-in.

Hence I will keep looking for the answers for the JQL filters.

0 votes
Dipak Naokar June 19, 2022

@Charlie Misonne Thank you.

I am looking for the solution using the JQL. (Not plugin as that would cost a few $).

Similar problems are available here but they do not have an answer to my specific query.

https://community.atlassian.com/t5/Jira-Core-Server-questions/Exclude-weekends-in-JQL-Query/qaq-p/1030290

https://community.atlassian.com/t5/Jira-questions/Need-filter-for-tickets-not-updated-for-last-3-days-excluding/qaq-p/1437566

Dipak Naokar June 19, 2022

@Phill Fox 

@Steph Sjostrom 

Can you please help on this ?

0 votes
Charlie Misonne
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
June 17, 2022

Hi @Dipak Naokar

This questions has been tagged with Jira Core so I asumme you are not using Jira Service Management. Are you aware that Jira Service Management has out of the box SLA's that can run in a certain time period (during business hours, week days only, ...).

If you can't use service management:

Here is a feature request similar to your question: JRASERVER-27539
A workaround with the endofweek() function is being discussed but I don't think it applies to you.
There might be plugins capable of it but I doubt you can do it with default JQL only.

Dipak Naokar June 17, 2022

@Charlie Misonne

Thanks for the quick response.

Actually the question is for "Jira software Cloud".

Let me check , if I can add or modify the tags.

Charlie Misonne
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
June 17, 2022

I can see you managed to change the tags. I also moved you question to Jira Software.

The default SLA's I mentioned are only available in Jira Service Management (not Jira Software) and support calendars to run an SLA in.

With Jira Software only you will definitely need some plugin.

Suggest an answer

Log in or Sign up to answer