List all currently open tickets but exclude one ticket type

[brigmill]

Greetings,

We've been using LanSweeper as our IT Helpdesk ticketing software and we pretty much love it. Thank you.

We've been creating pretty ok reports even though we're hardware guys, and we seem to be stuck on something simple we've been working on for a few days. We want a report that emails out every Monday that has every ticket that hasn't been closed yet, except for the programmer's development bug tickets (the type which is called Bug Tracker (Dev Only)). This is the script we use (below), and I think I could just include the 8 other ticket types if I wanted to go the longer route - but I'm wondering if there's a way I can just exclude (maybe "not like" somewhere???) the Bug Tracker (Dev Only) tickets. Any help would be appreciated. Thanks!

Select Top 1000000 htblticket.ticketid As ID,
htblticket.subject As Subject,
htblticket.date As [Creation Date],
htblticketstates.statename As Status,
htbltickettypes.typename As Type,
htblsource.name As Source,
htblusers.name As [User],
htblusers1.name As Agent,
htblsource.icon As icon,
DateDiff(day, htblticket.date, GetDate()) As [Days Since Creation]
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
Where (htblticketstates.statename = 'Open') Or
(htblticketstates.statename = 'In Progress') Or
(htblticketstates.statename = 'Awaiting Reply')
Order By Agent,
[Days Since Creation] Desc

[brigmill]

I greatly appreciate this query. One last question, I hope, and it's something I just can't figure out. We also want to exclude the ticket type called Change Control Form - whether the status is Open, Awaiting Reply or In Progress - but no matter how I add it in there (through the query itself or the report builder), it still shows up in the report results. So, basically, the Bug Tracker is currently excluded, which is fine, but we also want to exclude Change Control Form, which I've tried to do multiple ways and it just doesn't work.

[Nick_VDB]

You were on the right track for implementing your report. The ticket types are stored in the htbtickettypes.typename field so a 'not like' criteria can be added to not give back certain ticket types. To do a not like you can use '!=' or '<>'. In your situation we have added != 'Bug Tracker (Dev Only)' to the criteria.

Select Top 1000000 htblticket.ticketid As ID,
  htblticket.subject As Subject,
  htblticket.date As [Creation Date],
  htblticketstates.statename As Status,
  htbltickettypes.typename As Type,
  htblsource.name As Source,
  htblusers.name As [User],
  htblusers1.name As Agent,
  htblsource.icon As icon,
  DateDiff(day, htblticket.date, GetDate()) As [Days Since Creation]
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
Where (htblticketstates.statename = 'Open' Or htblticketstates.statename =
    'In Progress' Or htblticketstates.statename = 'Awaiting Reply') And
  htbltickettypes.typename != 'Bug Tracker (Dev Only)'
Order By Agent,
  [Days Since Creation] Desc