Differences

This shows you the differences between two versions of the page.

--- vendors:cisco:uc:icm:sql:ewt [2023/02/02 12:48] – gerardorourke
+++ vendors:cisco:uc:icm:sql:ewt [2023/02/02 14:52] (current) – [How to automate above] gerardorourke
@@ Line 387: / Line 387: @@
-===== Percentage of Agents in each Skillgropu =====
+===== Percentage of Agents in each Skillgroup =====
+==== ALL Skills - returned in SQL format ====
 <code sql>
 SET ARITHABORT OFF SET ANSI_WARNINGS OFF SET NOCOUNT ON
@@ Line 422: / Line 424: @@
 ORDER BY Count(*) DESC, Skill_Group.EnterpriseName
 </code>
+==== Top 10 Skillgroups returned in JSON format ====
+<code>
+SET ARITHABORT OFF SET ANSI_WARNINGS OFF SET NOCOUNT ON
+SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
+DECLARE @SG_NAME VARCHAR(50);
+SET @SG_NAME = 'DS_Card_Services.SG';
+DECLARE @SG_AGENTCOUNT INT;
+SET @SG_AGENTCOUNT = (SELECT Count(1) FROM Agent
+INNER JOIN Skill_Group_Member ON Agent.SkillTargetID = Skill_Group_Member.AgentSkillTargetID
+INNER JOIN Skill_Group ON Skill_Group_Member.SkillGroupSkillTargetID = Skill_Group.SkillTargetID
+WHERE (Agent.Deleted = 'N') and Skill_Group.EnterpriseName in (@SG_NAME))
+SELECT TOP (10)
+Skillgroup = Skill_Group.EnterpriseName
+,Count = Count(*)
+,AgentPercent = (1.0*Count(*)/@SG_AGENTCOUNT)
+,AgentPercent_ROUNDED = CONVERT(DECIMAL(10,2),(1.0*Count(*)/@SG_AGENTCOUNT))
+FROM
+Agent
+INNER JOIN Skill_Group_Member ON Agent.SkillTargetID = Skill_Group_Member.AgentSkillTargetID
+INNER JOIN Skill_Group ON Skill_Group_Member.SkillGroupSkillTargetID = Skill_Group.SkillTargetID
+WHERE
+Agent.PeripheralNumber in
+(SELECT Agent.PeripheralNumber FROM Agent
+INNER JOIN Skill_Group_Member ON Agent.SkillTargetID = Skill_Group_Member.AgentSkillTargetID
+INNER JOIN Skill_Group ON Skill_Group_Member.SkillGroupSkillTargetID = Skill_Group.SkillTargetID
+WHERE (Agent.Deleted = 'N') and Skill_Group.EnterpriseName in (@SG_NAME))
+GROUP BY Skill_Group.EnterpriseName
+ORDER BY Count(*) DESC, Skill_Group.EnterpriseName
+FOR JSON AUTO
+</code>
+==== How to automate above ====
+CVP have 5 variables it can return. External VXM[0-3] + Caller Input.\\
+Lets say to keep things simple we return a skillgroup and a percentage variable for the top 6 Skillgroups.
+The first skillgroup we don't need to return as we already know it will always have 100% percent.
+Each variable will return Skill group Name and percent, e.g. "MySkillName_SG,0.95"
+If there is less than 6 skills return a variable you can check such as "No_more_skills" or empty string etc,
+So we get the next 5 skills and return these to ICM\\.
+In ICM we
+  *Get using Custom Function the EWT for the core Skillgroup (<SG_Name>, <SG_percent>) - which uses this to get the EW and Store in PV6
+  *parse each of the 5 variable and then calcualte the EWT for that SG and add it to the value of PV6
+  *Repeat for each of the 5 variables
+CVP is grabbing this json file from a web server - which has a scheduled task which runs every X mins or once a day etc.
+The Script on the Web Server uses the above SQL query and outputs to a static text file.
+If this was a linux server you could do something like use Redis or memcache - but above also works and is very simple to do.
+the Script would run the SQL query for a predefined list of Skillgroups. And the output would be "SkillgroupName.json"
+Reference: https://learn.microsoft.com/en-us/sql/tools/sqlcmd/sqlcmd-utility?view=sql-server-ver16