→ 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: 
Null
Engaged Sweeper II
What change do I need to make so this report shows the initial ticket description (the first email they write in to describe a problem) in the Resolution column, as opposed to the final ticket note? I will change the column name to initial issue.

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


From having searched on here I found this but I am unable to get the two to work together
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
1 REPLY 1
Esben_D
Lansweeper Employee
Lansweeper Employee
Try the report below. Since what you want is actually easier than the report you listed, you can remove the subquery and just add the table holding the notes (htblnotes) with the condition that the note must be of the type 3 (which is the initial note).

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