Showing results for 
Show  only  | Search instead for 
Did you mean: 
Engaged Sweeper
I'm running a report for All tickets, including a custom field result for appropriate tickets. I get the results I want using Case When, but the report also pulls a duplicate, blank result for the tickets with that custom field result.

Select Distinct Top (20000) '#' + Cast(htblticket.ticketid As nvarchar) As
Ticket, As Dept,
DateDiff(day,, GetDate()) As [Days Open],
Case When UI_Ticket.fieldid = '253' Then End As [UI Ticket],
[User].name As [User],
htblticket.subject As Subject,
htblticketstates.statename As Status, As Agent,
'../helpdesk/icons/' + htbltickettypes.icon As icon,
htbltickettypes.typename As Type
From htblticket
Inner Join htblticketstates On htblticketstates.ticketstateid =
Inner Join htblusers [User] On [User].userid = htblticket.fromuserid
Left Join htblagents AgentID On AgentID.agentid = htblticket.agentid
Left Join htblusers AgentName On AgentName.userid = AgentID.userid
Inner Join htbltickettypes On htblticket.tickettypeid =
Inner Join htblsource On htblticket.sourceid = htblsource.sourceid
Inner Join htblticketcustomfield Department On htblticket.ticketid =
Inner Join htblticketcustomfield htblticketcustomfield1
On htblticket.ticketid = htblticketcustomfield1.ticketid
Inner Join htblticketcustomfield UI_Ticket On htblticket.ticketid =
Where htblticketstates.statename <> 'Closed' And htbltickettypes.typename <>
'Project Request' And Department.fieldid = '201'
Order By Dept,
[Days Open] Desc

I'm not sure how to avoid the duplicate ticket with blank result. Thanks from a SQL newb.