→ 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: 
zbx
Engaged Sweeper

Just finished writing a report that I've seen a few requests for.  Tickets closed by Agent in the last 7 days.  You'll have to find your own AgentID's within the database that correlate to your Agents (I've left ours in for the example). 

The report will look like:

Agent Name     Tickets Closed
Bob Barker           44
Bruce Wayne       37
Tom Cruise          28
Clark Kent           11
Robert Palmer     4
Ben Stiller            0

If you wish to exclude certain Agent's instead of including them, you can change:

 

 

Where htblagents.agentid In ('85', '19', '20', '73', '87', '18', '21', '32')

 

 

to

 

 

Where htblagents.agentid Not In ('85', '19', '20', '73', '87', '18', '21', '32',
  '1', '14', '23')

 

 


In this above example, I've excluded the Built-In account and System account as well as 1 additional Agent.

Below, please find the code for the Report.

 

 

Select Top 1000000 htblusers.name As [Agent Name],
  IsNull(Hist.[Tickets Closed], 0) As [Tickets Closed]
From htblagents
  Left Join htblusers htblusers On htblusers.userid = htblagents.userid
  Left Join (Select count(*) As [Tickets Closed],
      t1.[Agent Name]
    From (Select htblhistory.ticketid,
          htblhistory.agentid,
          htblusers.name As [Agent Name]
        From htblhistory
          Left Join htblagents On htblagents.agentid = htblhistory.agentid
          Left Join htblusers htblusers On htblusers.userid = htblagents.userid
        Where htblhistory.ticketstateid = 1 And htblhistory.date > GetDate() - 7
          And (htblhistory.typeid = 1 Or htblhistory.typeid = 30 Or
            htblhistory.typeid = 50)
        Group By htblhistory.ticketid,
          htblhistory.agentid,
          htblusers.name) t1
    Group By t1.[Agent Name]) Hist On Hist.[Agent Name] = htblusers.name
Where htblagents.agentid In ('85', '19', '20', '73', '87', '18', '21', '32')
Order By [Tickets Closed] Desc

 

 

 Hope someone else can find this useful!

0 REPLIES 0