Stale Tickets

[brodiemac2]

I would like to create a stale ticket report that shows all tickets that have had no update in 7 days or more. I'm basing it on the report of tickets worked on during the last 7 days. My report works but I get a lot of duplicates. What's your best suggestions for this kind of report? I would like to add the criteria that this exclude closed and ignored tickets. This is what I have:

Select Top 1000000 htblticket.ticketid,
  '#' + 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,
  htblhistorytypes.name As HistoryType,
  htblhistory.date As HistoryDate
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 htblhistory On htblticket.ticketid = htblhistory.ticketid
  Inner Join htblhistorytypes On htblhistorytypes.typeid = htblhistory.typeid
Where htblhistory.date < GetDate() - 7 And htblticket.spam <> 'True'
Order By HistoryDate Desc,
  htblticket.ticketid

[brodiemac2 · Accepted solution]

Tweaked it to this. Hope it helps someone who may need it:

Select Distinct Top 1000000 htblticket.ticketid,
  '#' + 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
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 htblhistory On htblticket.ticketid = htblhistory.ticketid
  Inner Join htblhistorytypes On htblhistorytypes.typeid = htblhistory.typeid
Where htblticket.updated < GetDate() - 7 And htblticketstates.statename <>
  'closed' And htblticket.spam <> 'True'
Order By LastUpdated Desc,
  htblticket.ticketid