SLA Breach Report

[JTempleton]

Good day!

I have modified the built-in Open Tickets with SLA overtime and removed a few fields. The report is only showing tickets that have their initial response time breached. How can I create a new report that will show tickets that have breached the more than just the initial SLA?

Select Top 1000000 '#' + Cast(htblticket.ticketid As nvarchar) As Ticket,
Convert(VarChar(10),htblticket.date,101) As CreationDate,
Convert(VarChar(10),htblticket.updated,101) 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 Case
When htblticket.slainitial < InitialResponseTime.FirstPublicReply Then 'Yes'
When InitialResponseTime.FirstPublicReply Is Null And
htblticket.slainitial < GetDate() Then 'Yes' Else 'No'
End = 'Yes' And (htblticketstates.statename = 'Open' And htblticket.spam <>
'True' Or htblticketstates.statename = 'New')
Order By htblticket.ticketid

[Esben_D]

I've reversed some of the logic in the report you gave to create a report which shows if a ticket breached the Resolve SLA.

The report takes the date of the last public note of a ticket and compares it to the slaresolve date.

Select Top 1000000 '#' + Cast(htblticket.ticketid As nvarchar) As Ticket,
  Convert(VarChar(10),htblticket.date,101) As CreationDate,
  Convert(VarChar(10),htblticket.updated,101) 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.slaresolved As ResolvedDateLimit,
  Case
    When htblticket.slaresolved < ResponseTime.LastPublicReply Then 'Yes'
    When ResponseTime.LastPublicReply Is Null And htblticket.slaresolved <
      GetDate() Then 'Yes'
    Else 'No'
  End As ResponseBreach
From htblticket
  Left Join (Select Top 1000000 htblnotes.ticketid,
        Max(htblnotes.date) As LastPublicReply
      From htblnotes
        Inner Join htblagents On htblnotes.userid = htblagents.userid
      Where htblnotes.notetype = 1
      Group By htblnotes.ticketid) As ResponseTime On htblticket.ticketid =
    ResponseTime.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 = 'Closed' And Case
    When htblticket.slaresolved < ResponseTime.LastPublicReply Then 'Yes'
    When ResponseTime.LastPublicReply Is Null And htblticket.slaresolved <
      GetDate() Then 'Yes'
    Else 'No'
  End = 'Yes' And DatePart(yy, htblticket.slaresolved) <> 1900 And
  htblticket.spam <> 'True'
Order By htblticket.ticketid