Help Desk Report Custom Fields

[hutchins79]

I Have added custom fields to Help Desk Tickets (Branch & Department). Trick is some branches do not have departments. So if I add the 'htblticketcustomfield2.tickettypefieldid = 48' (48 is our Department field) to the Where part I only get records with a 48. But I also want the records that meet the 45 & 47 (no department field) records. I might be tired and not seeing it...!


Select Top 1000000 htblticketcustomfield.ticketid As [Ticket Number],
htblusers.name As [Created By],
htblticket.date As [Created Date],
htblticketstates.statename As State,
htblusers1.name As Agent,
htblticket.slainitial As [Initial Response Date\Time limit],
htblticket.slaresolved As [Resolve time Date\Time limit],
htblticketcustomfield.data As Isssue,
htblticketcustomfield.tickettypefieldid,
htblticketcustomfield.fieldid,
htblticketcustomfield.ticketcustomfieldid,
htblticketcustomfield1.tickettypefieldid As tickettypefieldid1,
htblticketcustomfield1.data As Branch,
htblticketcustomfield2.tickettypefieldid As tickettypefieldid2,
htblticketcustomfield2.data As Department
From htblticket
Inner Join htblusers On htblusers.userid = htblticket.fromuserid
Inner Join htblticketstates On htblticketstates.ticketstateid =
htblticket.ticketstateid
Inner Join htblagents On htblagents.agentid = htblticket.agentid
Inner Join htblusers htblusers1 On htblusers1.userid = htblagents.userid
Inner Join htblticketcustomfield On htblticket.ticketid =
htblticketcustomfield.ticketid
Inner Join htblticketcustomfield htblticketcustomfield1
On htblticket.ticketid = htblticketcustomfield1.ticketid
Inner Join htblticketcustomfield htblticketcustomfield2
On htblticket.ticketid = htblticketcustomfield2.ticketid
Where (htblticketcustomfield.tickettypefieldid = 45 And
htblticketcustomfield1.tickettypefieldid = 47 And
htblticketcustomfield2.tickettypefieldid = 48 And htblticket.tickettypeid = 1)
Or
(htblticketcustomfield.tickettypefieldid = 45 And
htblticketcustomfield1.tickettypefieldid = 47 And htblticket.tickettypeid = 1)