Notification

Icon
Error

Report for a percentage of calls closed within SLA

Posted: Wednesday, March 8, 2017 3:40:37 PM(UTC)
poweld1

poweld1

Member Original PosterPosts: 102
0
Like
This issue has been solved! Click here to view the solution
Can I please have some help creating a report for a percentage of calls closed within SLA for each team?
Tom.P
#1Tom.P Member Administration Posts: 37  
posted: 3/10/2017 2:02:56 PM(UTC)
The following report will give you an overview per team of all closed tickets, the 'Closed on time' tickets and the calculated percentage.

Code:
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

poweld1
#2poweld1 Member Original PosterPosts: 102  
posted: 3/13/2017 9:11:47 AM(UTC)
Awesome, thanks for this.

Active Discussions

Lansweeper zerologin posted report
by  Antikas   Go to last post Go to first unread
Last post: Today at 9:42:54 AM(UTC)
Lansweeper Report doesn't show empty results for a field
by  AlexMZetec  
Go to last post Go to first unread
Last post: Yesterday at 3:43:08 PM(UTC)
Lansweeper Renamed Pcs / Laptops report
by  RC62N   Go to last post Go to first unread
Last post: Yesterday at 3:36:35 PM(UTC)
Lansweeper Servers without AV Report
by  Elwood472  
Go to last post Go to first unread
Last post: 9/27/2020 2:50:10 AM(UTC)
Lansweeper Adding Group by and Sum to Existing Report
by  RC62N  
Go to last post Go to first unread
Last post: 9/25/2020 3:43:49 PM(UTC)
Lansweeper Custom Fields on Report for Helpdesk Tickets
by  plangham_eurotech   Go to last post Go to first unread
Last post: 9/24/2020 2:43:41 PM(UTC)
Lansweeper September Patch Tuesday
by  Gilles B.  
Go to last post Go to first unread
Last post: 9/24/2020 7:47:49 AM(UTC)