‎09-29-2017 04:30 AM
Solved! Go to Solution.
‎10-10-2017 03:28 PM
Select Top 1000000 htblticket.ticketid,
'#' + Cast(htblticket.ticketid As nvarchar) As Ticket,
htblticket.date As CreationDate,
htblticket.updated As LastUpdated,
htbltickettypes.typename As TicketType,
htblticketstates.statename As State,
htblpriorities.name As Priority,
htblsource.name As Source,
htblusers.name As [User],
htblusers1.name As AssignedAgent,
'../helpdesk/icons/' + htbltickettypes.icon As icon,
htblticket.subject As Subject,
PurchaseType.data As PurchaseType,
POapproved.data As POapproved,
VendorSelection.data As VendorSelection,
PONumber.data As PONumber
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
Left Join (Select Top 1000000 htblticketcustomfield.ticketid,
htblticketcustomfield.data
From htblticketcustomfield
Inner Join htblcustomfields On htblcustomfields.fieldid =
htblticketcustomfield.fieldid
Where htblcustomfields.name Like '%Purchase Type%') As PurchaseType
On PurchaseType.ticketid = htblticket.ticketid
Left Join (Select Top 1000000 htblticketcustomfield.ticketid,
htblticketcustomfield.data
From htblticketcustomfield
Inner Join htblcustomfields On htblcustomfields.fieldid =
htblticketcustomfield.fieldid
Where htblcustomfields.name Like '%PO approved%') As POapproved
On POapproved.ticketid = htblticket.ticketid
Left Join (Select Top 1000000 htblticketcustomfield.ticketid,
htblticketcustomfield.data
From htblticketcustomfield
Inner Join htblcustomfields On htblcustomfields.fieldid =
htblticketcustomfield.fieldid
Where htblcustomfields.name Like '%Vendor Selection%') As VendorSelection
On VendorSelection.ticketid = htblticket.ticketid
Left Join (Select Top 1000000 htblticketcustomfield.ticketid,
htblticketcustomfield.data
From htblticketcustomfield
Inner Join htblcustomfields On htblcustomfields.fieldid =
htblticketcustomfield.fieldid
Where htblcustomfields.name Like '%PO Number%') As PONumber
On PONumber.ticketid = htblticket.ticketid
Where htbltickettypes.typename = 'IT Purchase' And htblticket.spam <> 'True'
‎07-20-2021 11:00 PM
‎10-17-2017 06:17 PM
‎10-10-2017 03:28 PM
Select Top 1000000 htblticket.ticketid,
'#' + Cast(htblticket.ticketid As nvarchar) As Ticket,
htblticket.date As CreationDate,
htblticket.updated As LastUpdated,
htbltickettypes.typename As TicketType,
htblticketstates.statename As State,
htblpriorities.name As Priority,
htblsource.name As Source,
htblusers.name As [User],
htblusers1.name As AssignedAgent,
'../helpdesk/icons/' + htbltickettypes.icon As icon,
htblticket.subject As Subject,
PurchaseType.data As PurchaseType,
POapproved.data As POapproved,
VendorSelection.data As VendorSelection,
PONumber.data As PONumber
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
Left Join (Select Top 1000000 htblticketcustomfield.ticketid,
htblticketcustomfield.data
From htblticketcustomfield
Inner Join htblcustomfields On htblcustomfields.fieldid =
htblticketcustomfield.fieldid
Where htblcustomfields.name Like '%Purchase Type%') As PurchaseType
On PurchaseType.ticketid = htblticket.ticketid
Left Join (Select Top 1000000 htblticketcustomfield.ticketid,
htblticketcustomfield.data
From htblticketcustomfield
Inner Join htblcustomfields On htblcustomfields.fieldid =
htblticketcustomfield.fieldid
Where htblcustomfields.name Like '%PO approved%') As POapproved
On POapproved.ticketid = htblticket.ticketid
Left Join (Select Top 1000000 htblticketcustomfield.ticketid,
htblticketcustomfield.data
From htblticketcustomfield
Inner Join htblcustomfields On htblcustomfields.fieldid =
htblticketcustomfield.fieldid
Where htblcustomfields.name Like '%Vendor Selection%') As VendorSelection
On VendorSelection.ticketid = htblticket.ticketid
Left Join (Select Top 1000000 htblticketcustomfield.ticketid,
htblticketcustomfield.data
From htblticketcustomfield
Inner Join htblcustomfields On htblcustomfields.fieldid =
htblticketcustomfield.fieldid
Where htblcustomfields.name Like '%PO Number%') As PONumber
On PONumber.ticketid = htblticket.ticketid
Where htbltickettypes.typename = 'IT Purchase' And htblticket.spam <> 'True'
‎10-10-2017 03:27 AM
‎09-29-2017 09:18 PM
‎09-29-2017 08:57 PM
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now