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

[Dipak Naokar]

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.

[Nallareddy Manasa]

@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".

4.  for "Today" custom field as well , 

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]

@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]

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  .

[Olha Onofriichuk_SaaSJet]

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:

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]

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

[Dipak Naokar]

@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]

@Phill Fox 

@Steph Sjostrom 

Can you please help on this ?

[Charlie Misonne]

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]

@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]

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.