→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
AEMIT
Engaged Sweeper II
Has anyone had any luck reporting on auto closed tickets?

We'd like to add a Yes/No column to our existing variation of tickets closed in the last x days, however the value in the table doesn't stay for htblticket.autoclose, my guess is that it flips back to 0 after it finishes. The last note added only refers to the last real user and not system.

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],
htblusers2.name As UserLastNote,
htblticket.subject As Subject,
Max(htblhistory.date) As Closure
From htblticket
Inner Join htblpriorities On htblpriorities.priority = htblticket.priority
Inner Join htblticketstates On htblticketstates.ticketstateid =
htblticket.ticketstateid
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 htbltickettypes On htblticket.tickettypeid =
htbltickettypes.tickettypeid
Inner Join htblsource On htblticket.sourceid = htblsource.sourceid
Inner Join htblusers htblusers2 On htblticket.userid_lastnote =
htblusers2.userid
Inner Join htblhistory On htblticket.ticketid = htblhistory.ticketid
Inner Join htblhistorytypes On htblhistorytypes.typeid = htblhistory.typeid
Inner Join htblticketstates htblticketstates1 On
htblticketstates1.ticketstateid = htblhistory.ticketstateid
Where htbltickettypes.typename = 'IT Helpdesk' And htblticketstates.statename =
'Closed' And htblticket.spam <> 'True' And htblhistorytypes.name In
('Status changed', 'Note added and state changed',
'Internal note added and state changed') And htblticketstates1.statename =
'Closed'
Group By htblticket.ticketid,
'#' + Cast(htblticket.ticketid As nvarchar),
htblticket.date,
htblticket.updated,
htbltickettypes.typename,
htblticketstates.statename,
htblpriorities.name,
htblsource.name,
htblusers.name,
htblusers2.name,
htblticket.subject,
htblusers1.name,
htblticket.autoclose
Having Max(htblhistory.date) > GetDate() - 8
Order By Closure Desc,
htblticket.ticketid
0 REPLIES 0