SLA advanced SQL queries - how to write a query to show tickets within 20% of breaching of SLA goal

[Amy_Jones]

I am attempting to create an SQL query that will show any tickets that are within 20% of breaching the SLA goal I've put. For example: if the goal is 10 hours, I want to see which tickets are within 20% of breaching this - meaning which tickets have 2 hours left on the clock. 

so it'll be "Time to achieve resolution" < 20% of goal

but how do I write the '20% of goal' part?

Thanks in advance

[Petter Gonçalves · Accepted solution]

Hello @Amy_Jones

Welcome to Atlassian Community!

Unfortunately, it is not possible to search for tickets in a Service Desk project filtering by the percentage of a specific SLA metric, however, we clearly understand how your requirement would be useful for report purposes.

That being said, we created the following feature request to get this option implemented:

- Ability to query issues by percent of the remaining time 

Feel free to vote and watch the suggestion to increase its priority and also receive notifications about any updates.

As a workaround, you can still filter your SLA fields using fixed time instead of percentage, like in the query below:

"Time to achieve resolution" = remaining("3h")

Let us know if you have any questions.

[Tuncay Senturk _Snapbytes_]

Hi '@Amy_Jones 

Welcome to the community.

As @Petter Gonçalves  mentioned, there is already a ticket for your request, hopefully it will be ready. On the other hand, currently, you can achieve that by using an app on Atlassian Marketplace

Please note that I’m one of the folks behind Snapbytes, there might be other solutions out there. 

Time to SLA app comes with a simple SLA search function for that.

slaFunction <= remainingPercentage(10)
slaFunction > remainingPercentage(75)
slaFunction <= elapsedPercentage(20)

Kind regards