Trying to report on tickets that aren't closed

[pryan67]

I have this report, but I'm getting duplicate tickets listed, and when I eliminate the duplicates (using Excel) it only shows about half the number of tickets that the helpdesk shows when I filter with the same criteria


Any thoughts?


Select Distinct Top 1000000 htblticket.ticketid As ticketid1,
'#' + Cast(htblticket.ticketid As nvarchar) As Ticket,
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,
htblusers2.name As UserLastNote,
'../helpdesk/icons/' + htbltickettypes.icon As icon,
htblticket.subject As Subject,
htblticketcustomfield.fieldid,
htblticketcustomfield.data,
htblticketcustomfield.data As Location
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
Inner Join htblticketcustomfield On htblticket.ticketid =
htblticketcustomfield.ticketid
Where htblticketstates.statename Not In ('closed', 'resolved')
Order By ticketid1