→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
khancock1
Engaged Sweeper II
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
3 REPLIES 3
khancock1
Engaged Sweeper II
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
Lansweeper Employee
Lansweeper Employee
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
Engaged Sweeper II
Bump - Anybody have any ideas on this?