Find the total number of attributes and skillgroups in an agent team.

With the new 9.x release and the popularity of Finesse I’m seeing a lot more cases of latency/timeouts from the Finesse desktop.  A lot of these have been attributed to network issues, but I still feel that Finesse has been made way too sensitive to the network.  Makes me yearn for the good old days of CTIOS.  One thing which came up recently is to find out how many skillgroups and attributes/PQs an agent team has.  As the SRND alludes that having too many SG/PQs (>50) in a team will cause performance issues.  The following two queries will give you the total count of unique attributes and skillgroups in an agent team, all you need to do is know your agent team ID.

Attributes

SELECT count(distinct(awdb.dbo.t_Agent_Attribute.AttributeID)), COUNT(awdb.dbo.t_Agent_Attribute.AttributeID) FROM awdb.dbo.t_Agent_Attribute INNER JOIN t_Agent_Team_Member ON (t_Agent_Attribute.SkillTargetID = t_Agent_Team_Member.SkillTargetID)
WHERE t_Agent_Team_Member.AgentTeamID = ‘XYZ’

Skillgroups

SELECT count(distinct(awdb.dbo.t_Agent_Attribute.AttributeID)), COUNT(awdb.dbo.t_Agent_Attribute.AttributeID) FROM awdb.dbo.t_Agent_Attribute INNER JOIN t_Agent_Team_Member ON (t_Agent_Attribute.SkillTargetID = t_Agent_Team_Member.SkillTargetID)
WHERE t_Agent_Team_Member.AgentTeamID = ‘XYZ’

david

Determine Busy Hour From UCCE TCD

The busy call hour is a valuable piece of information in order to forecast how many phone lines you’re going to need as well as how many agents, however getting this information can be difficult and then ensuring that it’s accurate is a whole other matter.  Recently I ran into an issue and I wanted to get the busy hour for myself.  I don’t have any access to the provider records, so all I have is the TCD table… when all you have is a hammer the whole world looks like a nail.

First, I turned to Google, which pointed me to how to get the calls per second.  This is good information as you would imagine that the highest CPS would fall during the busiest hour.  The query mentioned is:

   1:  SELECT Time=CONVERT(char,DateTime,108), CPS=CONVERT(decimal(5,2), RouteCallDetailTo5/300.0)
   2:  FROM t_Logger_Meters
   3:  WHERE DateTime BETWEEN '01/01/2013 00:05' AND '01/02/2013 23:59'
   4:  ORDER BY Time

Second, I opened up SQL Studio and began running some queries and came up with the following:

   1:  SELECT DATEPART(HOUR, DateTime) AS [Hour of Day], COUNT(distinct RouterCallKey) 
   2:  FROM t_Termination_Call_Detail
   3:  WHERE DateTime > '01/01/2013 06:00' and DateTime < '01/01/2013 20:00'
   4:  GROUP BY DATEPART(HOUR, DateTime)
   5:  ORDER BY DATEPART(HOUR, DateTime)
 
This query assumes that every new call will receive its own RCK, so we can use this as our counter.  Next, the query breaks up the select statement by hour.  Finally, put the output in Excel and make a pretty graph.  Some caveats, this is very generic and over counts.  It doesn’t care about transfers, outbound calls, etc.  Next, the query doesn’t look at the routing client, so if you have a lot of UCM generated calls this might not help you very much.
~david