Find All Tickets Which Have Surpassed Their SLA Time
Make sure that users get responses within your SLA time. The service level agreement indicates the maximum time before users should get a response, initial or subsequent replies. This report provides a list of tickets which are on SLA overtime, meaning that those tickets should have been replied to. This allows you to prioritize these tickets easier and make sure users get their replies in time.
Open Tickets with SLA Overtime Query
Select Top 1000000 htblticket.ticketid,
'#' + Cast(htblticket.ticketid As nvarchar) As Ticket,
htblticket.date As CreationDate,
htblticket.updated As LastUpdated,
htbltickettypes.typename As Type,
htblticketstates.statename As State,
htblpriorities.name As Priority,
htblsource.name As Source,
htblusers.name As [User],
htblusers1.name As AssignedAgent,
htblusers2.name As UserLastNote,
'../helpdesk/icons/' + htbltickettypes.icon As icon,
htblticket.subject As Subject,
htblticket.slaname As SLA,
htblticket.slainitial As InitialResponseDateLimit,
Case
When htblticket.slainitial < InitialResponseTime.FirstPublicReply Then 'Yes'
When InitialResponseTime.FirstPublicReply Is Null And
htblticket.slainitial < GetDate() Then 'Yes'
Else 'No'
End As InitialResponseBreach
From htblticket
Left Join (Select Top 1000000 htblnotes.ticketid,
Min(htblnotes.date) As FirstPublicReply
From htblnotes
Inner Join htblagents On htblnotes.userid = htblagents.userid
Where htblnotes.notetype = 1
Group By htblnotes.ticketid) As InitialResponseTime On
htblticket.ticketid = InitialResponseTime.ticketid
Inner Join htblticketstates On htblticketstates.ticketstateid =
htblticket.ticketstateid
Inner Join htbltickettypes On htbltickettypes.tickettypeid =
htblticket.tickettypeid
Inner Join htblpriorities On htblpriorities.priority = htblticket.priority
Inner Join htblsource On htblsource.sourceid = htblticket.sourceid
Inner Join htblusers On htblusers.userid = htblticket.fromuserid
Left Join htblagents On htblagents.agentid = htblticket.agentid
Left Join htblusers htblusers1 On htblusers1.userid = htblagents.userid
Inner Join htblusers htblusers2 On
htblusers2.userid = htblticket.userid_lastnote
Where htblticketstates.statename = 'Open' And Case
When htblticket.slainitial < InitialResponseTime.FirstPublicReply Then 'Yes'
When InitialResponseTime.FirstPublicReply Is Null And
htblticket.slainitial < GetDate() Then 'Yes'
Else 'No'
End = 'Yes' And htblticket.spam <> 'True'
Order By htblticket.ticketid
