Helpdesk: Open tickets with SLA overtime (Built-in)

[Nick_VDB]

Added in v.6.0.100

The report below lists all open tickets that have an SLA breach on the initial response

The report will only list tickets that meet all of the following criteria:

• The ticket has not been set to ‘Ignore’.
  
• The initial response SLA has been breached.
  
• The ticket is set to the ‘Open’ state.
  
• The ticket has not been set to ‘Ignore’.
  

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 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'
Order By htblticket.ticketid