‎08-16-2018 10:35 PM
Select Top 1000000 htblticket.ticketid,
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,
htblticket.subject,
(Select Top 1 htblnotes.note From htblnotes
Where htblnotes.ticketid = htblticket.ticketid Order By
htblticket.date) As Resolution
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
Left Join htblticketcustomfield As resolution On htblticket.ticketid =
resolution.ticketid And
resolution.fieldid = (Select htblcustomfields.fieldid From htblcustomfields
Where htblcustomfields.name = 'Ticket Resolution')
Left Join htblticketcustomfield As closure On htblticket.ticketid =
closure.ticketid And closure.fieldid = (Select htblcustomfields.fieldid
From htblcustomfields Where htblcustomfields.name = 'Closure ID')
Where htblticket.date > GetDate() - 35
Order By closure.ticketid
select htblticket.ticketid, htblticket.subject, htblnotes.note AS description from htblticket INNER JOIN
htblagents ON htblagents.agentid = htblticket.agentid INNER JOIN
htblusers ON htblusers.userid = htblagents.userid
INNER JOIN htblnotes on htblnotes.ticketid = htblticket.ticketid
WHERE htblusers.name = 'Built-in admin' AND htblnotes.notetype = 3
‎08-20-2018 03:55 PM
Select Top 1000000 htblticket.ticketid,
htblticket.date As CreationDate,
htblticket.updated As LastUpdated,
htbltickettypes.typename As Type,
htblticketstates.statename As State,
htblpriorities.name As Priority,
htblusers.name As [User],
htblusers1.name As AssignedAgent,
htblticket.subject,
htblnotes.note As [Initial Ticket]
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 htblnotes On htblticket.ticketid = htblnotes.ticketid And
htblusers.userid = htblnotes.userid
Where htblticket.date > GetDate() - 35 And htblnotes.notetype = 3
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now