Report for a percentage of calls closed within SLA

[poweld1]

Can I please have some help creating a report for a percentage of calls closed within SLA for each team?

[poweld1]

Awesome, thanks for this.

[Tom_P · Accepted solution]

The following report will give you an overview per team of all closed tickets, the 'Closed on time' tickets and the calculated percentage.

Select 
  htblteams.teamname as team, 
  totalTickets.totalCount as [All Closed Tickets], 
  onTimeTickets.onTimeCount as [All Closed on Time],
  CONVERT(DECIMAL(16,2), ((onTimeTickets.onTimeCount * 1.0)/ (totalTickets.totalCount * 1.0)))*100 as [Percentage]
from htblteams
  inner join 
	(Select htblticketteam.teamid as teamIdAll, COUNT(htblticket.ticketId) as totalCount from htblticket
	  inner join htblticketteam on htblticketteam.ticketid = htblticket.ticketid
	  Where slaresolved is not null and ticketstateid = 1
	  group by htblticketteam.teamid) as totalTickets on totalTickets.teamIdAll = htblteams.teamid
  inner join
	(Select htblticketteam.teamid as teamIdOnTime, sum((CASE WHEN lastCloseDate.mdate < slaresolved then 0  when lastCloseDate.mdate = '1900-01-01 00:01:00.000' then 1 else 1 end)) as onTimeCount
	from htblticket  inner join (select max(date) as mdate, ticketId as mticket from htblhistory where ticketstateid = 1 group by ticketId) as lastCloseDate on lastCloseDate.mticket = htblticket.ticketid
	inner join htblticketteam on htblticketteam.ticketid = htblticket.ticketid
	Where slaresolved is not null
	group by htblticketteam.teamid) as onTimeTickets on onTimeTickets.teamIdOnTime = htblteams.teamid