SQL Query for % SLA Achieved by Team

[Mike Jones]

Hi there

I'm new to the forum - I was hoping someone might be able to help.  My SQL is not great and I'm trying to create a chart that will show me a monthly % SLA performance for Incidents and service requests grouped by each team.

I have been able to create a widget that shows the weekly % SLA performance for Incidents and SR using the following query:

FROM

h_itsm_requests, (SELECT COUNT(*) AS total FROM h_itsm_requests WHERE h_requesttype IN('Incident','Service Request') AND h_status = 'status.closed' AND h_dateclosed >= CURDATE() - INTERVAL DAYOFWEEK(CURDATE())+6 DAY AND h_withinresponse IN(1,0)) AS requests

WHERE

h_requesttype IN('Incident','Service Request') AND h_status = 'status.closed' AND h_dateclosed >= CURDATE() - INTERVAL DAYOFWEEK(CURDATE())+6 DAY AND h_withinresponse = 1

 

Is there a way that I can add to this query to show SLA performance for Incidents & SR grouped by team. or is there a better way?

Any help would be greatly appreciated.

Many Thanks

Mike

[samwoo]

Hi @Mike Jones,

I don't know if this is what you are looking to achieve...

But this is how I pulled it off...

 

Start Date:


 

End Date:


 

---------------------------------------------------- (Horizontal line...ish):

 

[Apps Team - RESPONSE %]:

 

[Apps Team - RESPONSE %] (Copied):

 

No. Within Response:

 

No. NOT Within Response:

[samwoo]

Sorry Mike, I re-read your requirements so the above is probably not helpful - I can't think of how it can be displayed in a chart at this time (it's the end of the day...) maybe someone else will have some ideas, or, alternatively you could explore writing a report, and using Power BI + R Script to script to receive the reports from Hornbill, and create charts for them if that's possible in your org.

[Mike Jones]

Hi @samwoo thanks so much for taking the time to reply - Been scratching my head on this one for a while.  I should be able to use this as a starter - hey maybe I just don't have a chart!!!!  I'll have a play with the above and let you know.   Thanks again for your help really appreciate it.