‎07-02-2018 04:53 PM
‎07-03-2018 04:21 PM
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
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now