Report showing specific ticket types

[khancock1]

Hello, I'm trying to write a SQL query that will be automatically ingested into another system so that we can trend tickets over time. I thought I had this working perfectly until we had a ticket category go to 0, in which case the category is not displayed in the results of the below query. Can someone suggest how to change this query so that it also report's when a category has 0 tickets? Thank you!

Select Count(htblticket.ticketstateid) As 'total', htblticketstates.statename
From htblticket
Full Outer Join htblticketstates On htblticketstates.ticketstateid =
htblticket.ticketstateid
Where ((htblticketstates.ticketstateid = '2') Or
(htblticketstates.ticketstateid = '3') Or
(htblticketstates.ticketstateid = '4') Or
(htblticketstates.ticketstateid = '8') Or
(htblticketstates.ticketstateid = '10')) And
(htblticket.tickettypeid != '14')
Group By htblticketstates.statename

[khancock1]

Not quite that simple.

I'm trying to get a count of all tickets with the given states (ex: open, pending etc) which are not of ticket type 14 (our IT Projects category) and include states with 0 tickets. It worked perfectly until I put in the And statement, which then does not report any states with 0 tickets.

Thanks

[Esben_D]

I presume you want a report that gives a count of all the tickets per state.

Try the report below. It count the number of tickets (with ticket ID) per state.

Select Top 1000000 Count(htblticket.ticketid) As Count,
  htblticketstates.statename
From htblticket
  Right Join htblticketstates On htblticketstates.ticketstateid =
    htblticket.ticketstateid
Group By htblticketstates.statename

[khancock1]

Bump - Anybody have any ideas on this?