cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
harringg
Champion Sweeper
When creating a Helpdesk Report like the one below, what changes would need to be made to include tickets that may not have all the fields filled in yet? But I still want those tickets included in the report.

So if my ticket has a value in 'SerialNumber', but not in 'ToteIDName', I'd still like included in the report.

Example:
<Rest of report headers>,'SerialNumber','ToteIDName'
,123456,ID01
,234567,ID01
,345678,

Select Distinct Top 1000000 htblticket.ticketid,
'#' + Cast(htblticket.ticketid As nvarchar) As Ticket,
htblticket.date,
htbltickettypes.typename,
htblticketstates.statename,
htblticket.subject,
htblusers1.name As [User Name],
htblusers.name As [Help Desk Agent Name],
SerialNumber.data As SerialNumber,
ToteIDName.data As ToteIDName
From htblticket
Left Join htblagents On htblagents.agentid = htblticket.agentid
Left Join htblusers On htblusers.userid = htblagents.userid
Inner Join htblusers htblusers1 On htblusers1.userid = htblticket.fromuserid
Left Join htblticketstates On htblticketstates.ticketstateid =
htblticket.ticketstateid
Inner Join htbltickettypes On htbltickettypes.tickettypeid =
htblticket.tickettypeid
Inner Join (Select Top 1000000 htblticketcustomfield.ticketid,
htblticketcustomfield.data
From htblticketcustomfield
Inner Join htblcustomfields On htblcustomfields.fieldid =
htblticketcustomfield.fieldid
Where htblcustomfields.name Like 'PCD-HDD Serial Number') As SerialNumber
On htblticket.ticketid = SerialNumber.ticketid
Inner Join (Select Top 1000000 htblticketcustomfield.ticketid,
htblticketcustomfield.data
From htblticketcustomfield
Inner Join htblcustomfields On htblcustomfields.fieldid =
htblticketcustomfield.fieldid
Where htblcustomfields.name Like 'PCD-HDD ToteID Name') As ToteIDName
On htblticket.ticketid = ToteIDName.ticketid
1 ACCEPTED SOLUTION
David_G
Lansweeper Employee
Lansweeper Employee
You will have to Left Join the subqueries you have created to list the ticket custom fields. With an Inner Join, you will only be able to see tickets where a value is found for your ticket custom field. For your convenience, I have added the adjusted version with Left Joins below.
Select Distinct Top 1000000 htblticket.ticketid,
'#' + Cast(htblticket.ticketid As nvarchar) As Ticket,
htblticket.date,
htbltickettypes.typename,
htblticketstates.statename,
htblticket.subject,
htblusers1.name As [User Name],
htblusers.name As [Help Desk Agent Name],
SerialNumber.data As SerialNumber,
ToteIDName.data As ToteIDName
From htblticket
Left Join htblagents On htblagents.agentid = htblticket.agentid
Left Join htblusers On htblusers.userid = htblagents.userid
Inner Join htblusers htblusers1 On htblusers1.userid = htblticket.fromuserid
Left Join htblticketstates On htblticketstates.ticketstateid =
htblticket.ticketstateid
Inner Join htbltickettypes On htbltickettypes.tickettypeid =
htblticket.tickettypeid
Left Join (Select Top 1000000 htblticketcustomfield.ticketid,
htblticketcustomfield.data
From htblticketcustomfield
Inner Join htblcustomfields On htblcustomfields.fieldid =
htblticketcustomfield.fieldid
Where htblcustomfields.name Like 'PCD-HDD Serial Number') As SerialNumber
On htblticket.ticketid = SerialNumber.ticketid
Left Join (Select Top 1000000 htblticketcustomfield.ticketid,
htblticketcustomfield.data
From htblticketcustomfield
Inner Join htblcustomfields On htblcustomfields.fieldid =
htblticketcustomfield.fieldid
Where htblcustomfields.name Like 'PCD-HDD ToteID Name') As ToteIDName
On htblticket.ticketid = ToteIDName.ticketid

View solution in original post

1 REPLY 1
David_G
Lansweeper Employee
Lansweeper Employee
You will have to Left Join the subqueries you have created to list the ticket custom fields. With an Inner Join, you will only be able to see tickets where a value is found for your ticket custom field. For your convenience, I have added the adjusted version with Left Joins below.
Select Distinct Top 1000000 htblticket.ticketid,
'#' + Cast(htblticket.ticketid As nvarchar) As Ticket,
htblticket.date,
htbltickettypes.typename,
htblticketstates.statename,
htblticket.subject,
htblusers1.name As [User Name],
htblusers.name As [Help Desk Agent Name],
SerialNumber.data As SerialNumber,
ToteIDName.data As ToteIDName
From htblticket
Left Join htblagents On htblagents.agentid = htblticket.agentid
Left Join htblusers On htblusers.userid = htblagents.userid
Inner Join htblusers htblusers1 On htblusers1.userid = htblticket.fromuserid
Left Join htblticketstates On htblticketstates.ticketstateid =
htblticket.ticketstateid
Inner Join htbltickettypes On htbltickettypes.tickettypeid =
htblticket.tickettypeid
Left Join (Select Top 1000000 htblticketcustomfield.ticketid,
htblticketcustomfield.data
From htblticketcustomfield
Inner Join htblcustomfields On htblcustomfields.fieldid =
htblticketcustomfield.fieldid
Where htblcustomfields.name Like 'PCD-HDD Serial Number') As SerialNumber
On htblticket.ticketid = SerialNumber.ticketid
Left Join (Select Top 1000000 htblticketcustomfield.ticketid,
htblticketcustomfield.data
From htblticketcustomfield
Inner Join htblcustomfields On htblcustomfields.fieldid =
htblticketcustomfield.fieldid
Where htblcustomfields.name Like 'PCD-HDD ToteID Name') As ToteIDName
On htblticket.ticketid = ToteIDName.ticketid