EWT TCD Report

Note - EWT value is set on PV6

SET ARITHABORT OFF SET ANSI_WARNINGS OFF SET NOCOUNT ON 
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
 
SELECT TOP (8001)
 CallStartDateTime = DATEADD(MINUTE,-TCD.TimeZone,TCD.StartDateTimeUTC)
,CallDisconectDateTime  = DATEADD(SECOND,TCD.HoldTime,(DATEADD(SECOND,TCD.TalkTime,(DATEADD(SECOND,TCD.RingTime,(DATEADD(SECOND,TCD.LocalQTime,(DATEADD(SECOND,TCD.NetQTime,(DATEADD(SECOND,TCD.DelayTime,(DATEADD(MINUTE,-TCD.TimeZone,TCD.StartDateTimeUTC)))))))))))))
,PeripheralCallKey = TCD.PeripheralCallKey
,TCDRouterCallKeys = CONCAT(TCD.RouterCallKeyDay,'-',TCD.RouterCallKey)
,ICRCallKey = TCD.ICRCallKey
,CallTypeID=TCD.CallTypeID
,CallTypeEnterpriseName=Call_Type.EnterpriseName
,ANI = TCD.ANI
,DNIS = TCD.DNIS
,DigitsDialed = TCD.DigitsDialed
,MRDomainEnterpriseName = Media_Routing_Domain.EnterpriseName
,AgentSkillTargetID = ISNULL(TCD.AgentSkillTargetID,'')
,AgentTeamID = ISNULL(Agent_Team.AgentTeamID,'')
,AgentTeam = ISNULL(Agent_Team.EnterpriseName,'Unassigned')
,AgentName = ISNULL(Person.LastName + ',' + Person.FirstName,'')
,AgentLoginName = ISNULL(Person.LoginName,'')
,AgentInstrumentPortNumber = TCD.InstrumentPortNumber
,SkillGroup_OR_PrecisionQName = ISNULL(Precision_Queue.EnterpriseName,ISNULL(Skill_Group.EnterpriseName,''))
,PeripheralName = Peripheral.EnterpriseName
,TCDRouteID = TCD.RouteID
,TCD.PeripheralCallType
,CASE TCD.PeripheralCallType
WHEN '1' THEN 'ACD IN'
WHEN '2' THEN 'Pre-Route ACD IN'
WHEN '4' THEN 'Transfer IN/OUT'
WHEN '6' THEN 'Other IN'
WHEN '7' THEN 'Auto OUT'
WHEN '9' THEN 'OUT'
WHEN '10' THEN 'Agent Inside'
WHEN '12' THEN 'Consult' 
WHEN '13' THEN 'Consult Offered' 
WHEN '15' THEN 'Conference' 
WHEN '17' THEN 'Preview'
WHEN '18' THEN 'Reserved'
WHEN '19' THEN 'Supervisor Assist' 
WHEN '20' THEN 'Emergency Call' 
WHEN '21' THEN 'Supervisor Monitor' 
WHEN '23' THEN 'Supervisor Barge In' 
WHEN '24' THEN 'Supervisor Intercept' 
WHEN '27' THEN 'Reservation Preview'
WHEN '28' THEN 'Reservation Preview Direct'
WHEN '29' THEN 'Reservation Predictive'
WHEN '30' THEN 'Reservation Callback'
WHEN '31' THEN 'Reservation Personal'
WHEN '32' THEN 'Customer Preview'
WHEN '33' THEN 'Customer Preview Direct'
WHEN '34' THEN 'Customer Predictive'
WHEN '35' THEN 'Customer Callback'
WHEN '36' THEN 'Customer Personal Callback'
WHEN '37' THEN 'Customer IVR'
WHEN '38' THEN 'Non-ACD Call Call type for Multi-Line Agent'
WHEN '39' THEN 'Play Agent Greeting' 
WHEN '40' THEN 'Record Agent Greeting'
WHEN '41' THEN 'Voice Callback'
ELSE CAST(TCD.PeripheralCallType AS VARCHAR(10))
END AS PeripheralCallTypeDesc
,CallDispositionFlag = TCD.CallDispositionFlag
,CASE TCD.CallDispositionFlag
WHEN '1' THEN 'HANDLED'
WHEN '2' THEN 'ABANDONED'
WHEN '3' THEN 'SHORT'
WHEN '4' THEN 'ERROR'
WHEN '5' THEN 'REDIRECTED'
WHEN '6' THEN 'REQUERY'
WHEN '7' THEN 'INCOMPLETE'
ELSE CAST(TCD.CallDispositionFlag AS VARCHAR(10))
END AS CallDispositionFlagDesc
,CallDisposition = TCD.CallDisposition
,CASE TCD.CallDisposition     
WHEN '1' THEN 'Abandoned in Network'
WHEN '3' THEN 'Abandoned Ring'
WHEN '4' THEN 'Abandoned Delay'
WHEN '6' THEN 'Abandoned On Hold'
WHEN '7' THEN 'Short Call'
WHEN '10' THEN 'Agent-initiated call was not Answered'
WHEN '13' THEN 'Handled without Transfer or Conf'
WHEN '14' THEN 'Handled by a non-agent'
WHEN '15' THEN 'Call Redirected '
WHEN '19' THEN 'RONA'
WHEN '20' THEN 'Outbound - No Ringback'
WHEN '21' THEN 'Outbound - Operator'
WHEN '22' THEN 'Outbound - No dial tone'
WHEN '23' THEN 'Outbound - Person Ans'
WHEN '24' THEN 'Outbound - FAX machine'
WHEN '26' THEN 'Outbound - Call was stopped before the customer picked up'
WHEN '27' THEN 'PIM detected an error condition or an event did not occur for a call for an extended period of time'
WHEN '28' THEN 'Blind Transfer'
WHEN '29' THEN 'Announced Transfer'
WHEN '30' THEN 'Conferenced'
WHEN '33' THEN 'Outbound - call was picked up by an answering machine'
WHEN '34' THEN 'Network Blind Transfer'
WHEN '35' THEN 'Task Abandoned in Router'
WHEN '36' THEN 'Task Abandoned Before Offered'
WHEN '37' THEN 'Task Abandoned While Offered'
WHEN '38' THEN 'Normal End Task'
WHEN '39' THEN 'Cannot Obtain Task ID'
WHEN '40' THEN 'Agent Logged Out During Task'
WHEN '41' THEN 'Maximum Task Lifetime Exceeded'
WHEN '42' THEN 'Application Path Went Down'
WHEN '52' THEN 'Called Party Disconnected'
WHEN '54' THEN 'Successful Single Step Transfer'
WHEN '55' THEN 'Network Consult Transfer'
WHEN '57' THEN 'Abandon Network Consult'
WHEN '58' THEN 'Router Requery Before Answer Router Received a Requery Event from CVP before the Agent PG indicated the call was answered by an agent.'
WHEN '59' THEN 'Router Requery After Answer Router Received a Requery Event from CVP after the Agent PG indicated the call was answered by an agent.'
WHEN '60' THEN 'Network Error Router received a Network Error for a call targeting an agent before the call arrived to the agent.'
WHEN '61' THEN 'Network Error Before Answer Router Received a Network Error Event from CVP before the Agent PG indicated the call was answered by an agent.'
WHEN '62' THEN 'Network Error After Answer'
ELSE CAST(TCD.CallDisposition AS VARCHAR(10))
END AS CallDispositionDesc
,Duration = TCD.Duration
,NetworkTime = TCD.NetworkTime
,EWT= TCD.Variable6
,NetQTime = TCD.NetQTime
,EwtDifference = ABS(TCD.Variable6-TCD.NetQTime)
,CASE 
WHEN (ABS(TCD.Variable6-TCD.NetQTime) < 60) THEN '< 60'
WHEN (ABS(TCD.Variable6-TCD.NetQTime) > 60 AND ABS(TCD.Variable6-TCD.NetQTime) < 180) THEN '60 - 180'
WHEN (ABS(TCD.Variable6-TCD.NetQTime) > 180 AND ABS(TCD.Variable6-TCD.NetQTime) < 300) THEN '180 - 300'
WHEN (ABS(TCD.Variable6-TCD.NetQTime) > 300 AND ABS(TCD.Variable6-TCD.NetQTime) < 600) THEN '300 - 600'
WHEN (ABS(TCD.Variable6-TCD.NetQTime) > 600) THEN '> 600'
ELSE 'N/A'
END AS 'EwtDifferenceGroup'
,QEwtRatio = (TCD.NetQTime * 1.0/TCD.Variable6) 
,CASE 
WHEN (TCD.NetQTime * 1.0/TCD.Variable6) < 0.25 THEN 'QTime < 25% of EWT'
WHEN (TCD.NetQTime * 1.0/TCD.Variable6) < 0.5 THEN 'QTime < 50% of EWT'
WHEN (TCD.NetQTime * 1.0/TCD.Variable6) < 0.75 THEN 'QTime < 75% of EWT'
WHEN ((TCD.NetQTime * 1.0/TCD.Variable6) > 0.75 AND (TCD.NetQTime * 1.0/TCD.Variable6) < 1.25) THEN 'QTime with 0.75 and 1.25 of EWT'
WHEN (TCD.NetQTime * 1.0/TCD.Variable6) > 10 THEN 'QTime > 10 times EWT'
WHEN (TCD.NetQTime * 1.0/TCD.Variable6) > 5 THEN 'QTime > 5 times EWT'
WHEN (TCD.NetQTime * 1.0/TCD.Variable6) > 3 THEN 'QTime > 3 times EWT'
WHEN (TCD.NetQTime * 1.0/TCD.Variable6) > 2 THEN 'QTime > 2 times EWT'
WHEN (TCD.NetQTime * 1.0/TCD.Variable6) > 1.75 THEN 'QTime > 1.75 times EWT'
WHEN (TCD.NetQTime * 1.0/TCD.Variable6) > 1.5 THEN 'QTime > 1.5 times EWT'
WHEN (TCD.NetQTime * 1.0/TCD.Variable6) > 1.25 THEN 'QTime > 1.25 times EWT'
ELSE 'Calculation Error'
END AS EwtPercentGroup
,LocalQTime = TCD.LocalQTime
,RingTime = TCD.RingTime
,DelayTime = TCD.DelayTime
,TimeToAband = TCD.TimeToAband
,TalkTime = TCD.TalkTime
,HoldTime = TCD.HoldTime
,CallDuration = (TCD.TalkTime+TCD.HoldTime)
,ConferenceTime = TCD.ConferenceTime
,WorkTime = TCD.WorkTime
,WrapupData = TCD.WrapupData
,CallSegmentTime = TCD.CallSegmentTime
,PV1 = TCD.Variable1
,PV2 = TCD.Variable2
,PV3 = TCD.Variable3
,PV4 = TCD.Variable4
,PV5 = TCD.Variable5
,PV6 = TCD.Variable6
,PV7 = TCD.Variable7
,PV8 = TCD.Variable8
,PV9 = TCD.Variable9
,PV10 = TCD.Variable10
,NewTransaction = TCD.NewTransaction -- Indicates there is at least one more TCD row for this call
,NetworkTargetID = TCD.NetworkTargetID
,TrunkGroupID = TCD.TrunkGroupID
,AgentPeripheralNumber = TCD.AgentPeripheralNumber
,AnsweredWithinServiceLevel = TCD.AnsweredWithinServiceLevel
,Trunk = TCD.Trunk
,CED=TCD.CED
,ApplicationTaskDisposition=TCD.ApplicationTaskDisposition
,ApplicationData=TCD.ApplicationData
,ECCPayloadID=TCD.ECCPayloadID
,CallTypeReportingDateTime=TCD.CallTypeReportingDateTime
,RoutedSkillGroupSkillTargetID=TCD.RoutedSkillGroupSkillTargetID
,RoutedServiceSkillTargetID=TCD.RoutedServiceSkillTargetID
,RoutedAgentSkillTargetID=TCD.RoutedAgentSkillTargetID
,Originated=TCD.Originated
,CallReferenceID=TCD.CallReferenceID
,CallGUID=TCD.CallGUID
 
  FROM 
  Termination_Call_Detail TCD (nolock)
  LEFT JOIN Media_Routing_Domain (nolock) ON (TCD.MRDomainID = Media_Routing_Domain.MRDomainID)
  LEFT JOIN Agent (nolock) ON (TCD.AgentSkillTargetID = Agent.SkillTargetID)
  LEFT JOIN Agent SAgent (nolock) ON (TCD.SourceAgentSkillTargetID = SAgent.SkillTargetID)
  LEFT JOIN Person (nolock) ON (Agent.PersonID = Person.PersonID)
  LEFT JOIN Person SPerson (nolock) ON (SAgent.PersonID = SPerson.PersonID)
  LEFT JOIN Agent_Team_Member (nolock) ON (Agent.SkillTargetID = Agent_Team_Member.SkillTargetID)
  LEFT JOIN Agent_Team (nolock) ON (Agent_Team_Member.AgentTeamID = Agent_Team.AgentTeamID)
  LEFT JOIN Skill_Group (nolock) ON (TCD.SkillGroupSkillTargetID = Skill_Group.SkillTargetID)
  LEFT JOIN Precision_Queue (nolock) ON (TCD.PrecisionQueueID = Precision_Queue.PrecisionQueueID)
  LEFT JOIN Call_Type (nolock) ON (TCD.CallTypeID = Call_Type.CallTypeID)
  LEFT JOIN Peripheral (nolock) ON (TCD.PeripheralID = Peripheral.PeripheralID)
 
 WHERE 
 
  TCD.DateTime >=  '2021-01-13 00:00:00'
  AND TCD.DateTime < '2021-01-13 23:59:59'
  AND TCD.CallTypeID IN (5293) 
  AND TCD.PeripheralID IN (5000) 
  AND TCD.Variable6 > 0
 
ORDER BY 
Call_Type.EnterpriseName, TCD.StartDateTimeUTC

SET ARITHABORT OFF SET ANSI_WARNINGS OFF SET NOCOUNT ON 
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
 
SELECT TOP (8001)
 CallTypeName = Call_Type.EnterpriseName
,StartDate = Format(TCD.StartDateTimeUTC,'yyyy-MM-dd')
--,EWT= TCD.Variable6
--,NetQTime = TCD.NetQTime
--,EwtDifference = ABS(TCD.Variable6-TCD.NetQTime)
,CASE 
WHEN (ABS(TCD.Variable6-TCD.NetQTime) < 60) THEN '< 60'
WHEN (ABS(TCD.Variable6-TCD.NetQTime) > 60 AND ABS(TCD.Variable6-TCD.NetQTime) < 180) THEN '60 - 180'
WHEN (ABS(TCD.Variable6-TCD.NetQTime) > 180 AND ABS(TCD.Variable6-TCD.NetQTime) < 300) THEN '180 - 300'
WHEN (ABS(TCD.Variable6-TCD.NetQTime) > 300 AND ABS(TCD.Variable6-TCD.NetQTime) < 600) THEN '300 - 600'
WHEN (ABS(TCD.Variable6-TCD.NetQTime) > 600) THEN '> 600'
ELSE 'N/A'
END AS 'EwtDifferenceGroup'
--,QEwtRatio = (TCD.NetQTime * 1.0/TCD.Variable6) 
,CASE 
WHEN (TCD.NetQTime * 1.0/TCD.Variable6) < 0.25 THEN 'QTime < 25% of EWT'
WHEN (TCD.NetQTime * 1.0/TCD.Variable6) < 0.5 THEN 'QTime < 50% of EWT'
WHEN (TCD.NetQTime * 1.0/TCD.Variable6) < 0.75 THEN 'QTime < 75% of EWT'
WHEN ((TCD.NetQTime * 1.0/TCD.Variable6) > 0.75 AND (TCD.NetQTime * 1.0/TCD.Variable6) < 1.25) THEN 'QTime with 0.75 and 1.25 of EWT'
WHEN (TCD.NetQTime * 1.0/TCD.Variable6) > 10 THEN 'QTime > 10 times EWT'
WHEN (TCD.NetQTime * 1.0/TCD.Variable6) > 5 THEN 'QTime > 5 times EWT'
WHEN (TCD.NetQTime * 1.0/TCD.Variable6) > 3 THEN 'QTime > 3 times EWT'
WHEN (TCD.NetQTime * 1.0/TCD.Variable6) > 2 THEN 'QTime > 2 times EWT'
WHEN (TCD.NetQTime * 1.0/TCD.Variable6) > 1.75 THEN 'QTime > 1.75 times EWT'
WHEN (TCD.NetQTime * 1.0/TCD.Variable6) > 1.5 THEN 'QTime > 1.5 times EWT'
WHEN (TCD.NetQTime * 1.0/TCD.Variable6) > 1.25 THEN 'QTime > 1.25 times EWT'
ELSE 'Calculation Error'
END AS EwtPercentGroup
 
  FROM 
  Termination_Call_Detail TCD (nolock)
  LEFT JOIN Call_Type (nolock) ON (TCD.CallTypeID = Call_Type.CallTypeID)
 
 WHERE 
 
  TCD.DateTime >=  '2021-01-13 00:00:00'
  AND TCD.DateTime < '2021-01-13 23:59:59'
  AND TCD.CallTypeID IN (5293) 
  AND TCD.PeripheralID IN (5000) 
  AND TCD.Variable6 > 0
 
ORDER BY 
Call_Type.EnterpriseName, TCD.StartDateTimeUTC

ValidValue(((SkillGroup.%1%.RouterCallsQNow)*trunc((ValidValue(SkillGroup.%1%.AvgHandledCallsTimeTo5,%2%))/max(SkillGroup.%1%.Ready,(SkillGroup.%1%.TalkingIn+     SkillGroup.%1%.TalkingOut+SkillGroup.%1%.TalkingOther)))),%2%)

userEWT_1SkillGroup(Sales_IVR,406) + trunc((0.98)*userEWT_1SkillGroup(CustService_IVR,376)) + trunc((0.75)*userEWT_1SkillGroup(MySkillgroup,278))

ValidValue(((PQ.%1%.CallsInQ)*trunc((ValidValue(PQ.%1%.AvgHandledCallsTimeTo5,%2%))/max(PQ.%1%.Ready,(PQ.%1%.TalkingIn+PQ.%1%.TalkingOther)))),%2%)

userEWT_1PQ(Sales_PQ,245) + userEWT_1PQ(Service_PQ,245)

SELECT 
	SGRT.[SkillTargetID]
	,SG.EnterpriseName
      ,[DateTime]
      ,[RouterCallsQNow]
      ,[AvgHandledCallsTimeTo5]
      ,[Ready]
      ,[TalkingIn]
      ,[TalkingOther]
      ,[TalkingOut]
      ,[RouterLongestCallInQ]
  FROM 
  [vhi_awdb].[dbo].[Skill_Group_Real_Time] SGRT,
  Skill_Group SG
 
  WHERE SGRT.SkillTargetID = SG.SkillTargetID
  ORDER BY AvgHandledCallsTimeTo5 DESC
 

SET ARITHABORT OFF SET ANSI_WARNINGS OFF SET NOCOUNT ON 
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
 
;WITH TCD_EWT AS (SELECT 
 CallTypeName = Call_Type.EnterpriseName
,StartDate = Format(TCD.StartDateTimeUTC,'yyyy-MM-dd')
--,EWT= TCD.Variable6
--,NetQTime = TCD.NetQTime
--,EwtDifference = ABS(TCD.Variable6-TCD.NetQTime)
,CASE 
WHEN (ABS(TCD.Variable6) >= 0 AND ABS(TCD.Variable6) < 300) THEN '0 - 5 min'
WHEN (ABS(TCD.Variable6) >= 300 AND ABS(TCD.Variable6) < 600) THEN '5 - 10 min'
WHEN (ABS(TCD.Variable6) >= 600 AND ABS(TCD.Variable6) < 900) THEN '10 - 15 min'
WHEN (ABS(TCD.Variable6) >= 900 AND ABS(TCD.Variable6) < 1200) THEN '15 - 20 min'
WHEN (ABS(TCD.Variable6) >= 1200 AND ABS(TCD.Variable6) < 1800) THEN '20 - 30 min'
WHEN (ABS(TCD.Variable6) >= 1800 AND ABS(TCD.Variable6) < 2700) THEN '30 - 45 min'
WHEN (ABS(TCD.Variable6) >= 2700 AND ABS(TCD.Variable6) < 3600) THEN '45 - 60 min'
WHEN (ABS(TCD.Variable6) >= 3600) THEN '60 min and above'
ELSE 'N/A'
END AS 'EWTGroup'
 
,CASE 
WHEN (ABS(TCD.Variable6-TCD.NetQTime) < 60) THEN '< 60'
WHEN (ABS(TCD.Variable6-TCD.NetQTime) >= 60 AND ABS(TCD.Variable6-TCD.NetQTime) < 180) THEN '60 - 180'
WHEN (ABS(TCD.Variable6-TCD.NetQTime) >= 180 AND ABS(TCD.Variable6-TCD.NetQTime) < 300) THEN '180 - 300'
WHEN (ABS(TCD.Variable6-TCD.NetQTime) >= 300 AND ABS(TCD.Variable6-TCD.NetQTime) < 600) THEN '300 - 600'
WHEN (ABS(TCD.Variable6-TCD.NetQTime) >= 600) THEN '> 600'
ELSE 'N/A'
END AS 'EwtDifferenceGroup'
--,QEwtRatio = (TCD.NetQTime * 1.0/TCD.Variable6) 
,CASE 
WHEN (TCD.NetQTime * 1.0/TCD.Variable6) < 0.25 THEN 'QTime < 25% of EWT'
WHEN (TCD.NetQTime * 1.0/TCD.Variable6) < 0.5 THEN 'QTime < 50% of EWT'
WHEN (TCD.NetQTime * 1.0/TCD.Variable6) < 0.75 THEN 'QTime < 75% of EWT'
WHEN ((TCD.NetQTime * 1.0/TCD.Variable6) > 0.75 AND (TCD.NetQTime * 1.0/TCD.Variable6) < 1.25) THEN 'QTime with 0.75 and 1.25 of EWT'
WHEN (TCD.NetQTime * 1.0/TCD.Variable6) > 10 THEN 'QTime > 10 times EWT'
WHEN (TCD.NetQTime * 1.0/TCD.Variable6) > 5 THEN 'QTime > 5 times EWT'
WHEN (TCD.NetQTime * 1.0/TCD.Variable6) > 3 THEN 'QTime > 3 times EWT'
WHEN (TCD.NetQTime * 1.0/TCD.Variable6) > 2 THEN 'QTime > 2 times EWT'
WHEN (TCD.NetQTime * 1.0/TCD.Variable6) > 1.75 THEN 'QTime > 1.75 times EWT'
WHEN (TCD.NetQTime * 1.0/TCD.Variable6) > 1.5 THEN 'QTime > 1.5 times EWT'
WHEN (TCD.NetQTime * 1.0/TCD.Variable6) > 1.25 THEN 'QTime > 1.25 times EWT'
ELSE 'Calculation Error'
END AS EwtPercentGroup
 
  FROM 
  Termination_Call_Detail TCD (nolock)
  LEFT JOIN Call_Type (nolock) ON (TCD.CallTypeID = Call_Type.CallTypeID)
 
 WHERE 
 
  TCD.DateTime >=  '2022-07-07 00:00:00'
  AND TCD.DateTime < '2022-07-07 23:59:59'
  AND TCD.CallTypeID IN (5027,5080,5136,5156) 
  AND TCD.PeripheralID IN (5000) 
  AND isNumeric(TCD.Variable6) = '1'
  AND TCD.Variable6 > 0
)
 
SELECT 
 
CallTypeName,
StartDate,
EWTGroup,
EwtDifferenceGroup,
EwtPercentGroup,
Calls = COUNT(*)
 
FROM TCD_EWT
 
GROUP BY CallTypeName, StartDate, EWTGroup, EwtDifferenceGroup, EwtPercentGroup
ORDER BY CallTypeName, StartDate, EWTGroup, EwtDifferenceGroup, EwtPercentGroup

SET ARITHABORT OFF SET ANSI_WARNINGS OFF SET NOCOUNT ON 
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
 
DECLARE @SG_NAME VARCHAR(50);
SET @SG_NAME = 'My_SkillgroupName';
 
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        
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

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

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