UCCE Config Queries
ICR_Globals is the table where system default infor is stored, such as default CallType threshold
Agent
SELECT Agent.SkillTargetID, Agent.PeripheralID, Agent.PeripheralNumber, Agent.PersonID, Person.FirstName, Person.LastName, Person.LoginName, Agent_Team.EnterpriseName AS AgentTeamName FROM Agent LEFT JOIN Agent_Team_Member ON Agent.SkillTargetID = Agent_Team_Member.SkillTargetID LEFT JOIN Agent_Team ON Agent_Team_Member.AgentTeamID = Agent_Team.AgentTeamID LEFT JOIN Person ON Agent.PersonID = Person.PersonID ORDER BY FirstName
Call types
SELECT CallTypeID = CT.CallTypeID ,CallTypeName = CT.EnterpriseName ,Description = CT.Description ,SLThreshold = CT.ServiceLevelThreshold ,BucketName = isNull(BI.EnterpriseName,'Default') ,SL_Type = [ServiceLevelType], CASE [ServiceLevelType] When '0' THEN 'Default' When '1' THEN 'Ignore Abandoned Calls' When '2' THEN 'Abandoned Calls Have Negative Impact' When '3' THEN 'Abandoned Calls Have Positive Impact' ELSE 'Unknown' END AS SL_Type_Desc FROM Call_Type CT LEFT JOIN Bucket_Intervals BI ON CT.BucketIntervalID = BI.BucketIntervalID WHERE CT.Deleted = 'N' ORDER By CT.EnterpriseName
Call Type <> Script mapping
SELECT CallTypeID = [Call_Type_Map].[CallTypeID], MasterScriptID = [Call_Type_Map].[MasterScriptID], CallTypeName = [Call_Type].[EnterpriseName], MasterScriptName = [Master_Script].[EnterpriseName] FROM [ucce_awdb].[dbo].[Call_Type_Map], [ucce_awdb].[dbo].[Master_Script], [ucce_awdb].[dbo].[Call_Type] WHERE [Call_Type_Map].[MasterScriptID] = [Master_Script].[MasterScriptID] AND [Call_Type_Map].[CallTypeID] = [Call_Type].[CallTypeID] ORDER BY CallTypeName
Dialed Number <> Calltype Mapping
SELECT Dialed_Number_Map.DialedNumberID, Dialed_Number.DialedNumberID AS DialedNumberID, Dialed_Number.EnterpriseName, Dialed_Number.RoutingClientID, Dialed_Number.DialedNumberString, Dialed_Number.Description, Call_Type.CallTypeID, Call_Type.EnterpriseName AS CalltypeName, Call_Type.Description AS CalltypeDesc, Routing_Client.RoutingClientID AS RoutingClientID, Routing_Client.EnterpriseName AS RoutingClientName FROM Dialed_Number_Map INNER JOIN Dialed_Number ON Dialed_Number_Map.DialedNumberID = Dialed_Number.DialedNumberID INNER JOIN Call_Type ON Dialed_Number_Map.CallTypeID = Call_Type.CallTypeID INNER JOIN Routing_Client ON Dialed_Number.RoutingClientID = Routing_Client.RoutingClientID ORDER By DialedNumberString
Dialed Number <> Calltype <> Script
SELECT DNID = Dialed_Number.DialedNumberID, DNString = Dialed_Number.DialedNumberString, DNName = Dialed_Number.EnterpriseName, DNRoutingClientID = Dialed_Number.RoutingClientID, DNDesc = ISNULL(Dialed_Number.Description,''), RoutingClientID = Routing_Client.RoutingClientID, RoutingClientName = Routing_Client.EnterpriseName, CallTypeID = ISNULL([Call_Type_Map].[CallTypeID],''), CallTypeName = ISNULL([Call_Type].[EnterpriseName],''), CallTypeDesc = ISNULL(Call_Type.Description,''), MasterScriptID = ISNULL([Call_Type_Map].[MasterScriptID],''), MasterScriptName = ISNULL([Master_Script].[EnterpriseName],'') FROM Master_Script RIGHT OUTER JOIN Routing_Client RIGHT OUTER JOIN Call_Type INNER JOIN Dialed_Number_Map ON Call_Type.CallTypeID = Dialed_Number_Map.CallTypeID LEFT OUTER JOIN Call_Type_Map ON Call_Type.CallTypeID = Call_Type_Map.CallTypeID RIGHT OUTER JOIN Dialed_Number ON Dialed_Number_Map.DialedNumberID = Dialed_Number.DialedNumberID ON Routing_Client.RoutingClientID = Dialed_Number.RoutingClientID ON Master_Script.MasterScriptID = Call_Type_Map.MasterScriptID ORDER By Dialed_Number.DialedNumberString, Routing_Client.EnterpriseName
Skill Groups / PQ
SELECT [SkillTargetID] ,[PrecisionQueueID] ,[PeripheralID] ,[EnterpriseName] ,[PeripheralNumber] ,[PeripheralName] ,[BaseSkillTargetID] ,[SubGroupMaskType] ,[Description] ,[MRDomainID] ,[IPTA] ,[DefaultEntry] ,[ServiceLevelThreshold] ,[ServiceLevelType] ,[BucketIntervalID] ,[DepartmentID] FROM [ucce_awdb].[dbo].[Skill_Group]
Reason Codes
SELECT [ReasonCodeID] ,[ReasonCode] ,[ReasonText] ,[Description] FROM [ucce_awdb].[dbo].[Reason_Code]
Dialed Numbers, CallTypes, Skillgroups, Count
SET ARITHABORT OFF SET ANSI_WARNINGS OFF SET NOCOUNT ON SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT TOP (8001) DigitsDialed = TCD.DigitsDialed ,CallTypeID=TCD.CallTypeID ,CallTypeEnterpriseName=Call_Type.EnterpriseName ,SkillGroupName = Skill_Group.EnterpriseName ,RoutedSkillGroupSkillTargetID=TCD.RoutedSkillGroupSkillTargetID ,Total = COUNT(*) FROM Termination_Call_Detail (nolock) TCD LEFT JOIN Peripheral (nolock) ON (TCD.PeripheralID = Peripheral.PeripheralID) LEFT JOIN Media_Routing_Domain (nolock) ON (TCD.MRDomainID = Media_Routing_Domain.MRDomainID) LEFT JOIN Skill_Group (nolock) ON (TCD.RoutedSkillGroupSkillTargetID = 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) WHERE TCD.DateTime > GetDate()-10 AND TCD.PeripheralID IN (5002) AND TCD.CallTypeID <> -1 -- Removes Outbound Calls AND TCD.DigitsDialed IS NOT NULL GROUP BY TCD.DigitsDialed, TCD.CallTypeID, Call_Type.EnterpriseName, Skill_Group.EnterpriseName, TCD.RoutedSkillGroupSkillTargetID ORDER BY COUNT(*) DESC, TCD.DigitsDialed, Call_Type.EnterpriseName, Skill_Group.EnterpriseName