cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
2943
Views
0
Helpful
12
Replies
Stuart Cross
Beginner

UCCE skill group SQL query

Hi guys, 

I'm try to see all agents available in a particular skillgroup, real time.  It would be handy to be able to see there status also.  Does anyone know an SQL query to run that will show me this?

 

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions

yes, try this

Select 
(Select EnterpriseName from Agent a where a.SkillTargetID = asg.SkillTargetID) as AgentName,
(Select FirstName from Person p where p.PersonID = (Select PersonID from Agent a where a.SkillTargetID = asg.SkillTargetID)) as Firstname,
(Select LastName from Person p where p.PersonID = (Select PersonID from Agent a where a.SkillTargetID = asg.SkillTargetID)) as Lastname,
(case when asg.AgentState = '0' then 'LoggedOff'
when asg.AgentState = '1' then 'LoggedOn'
when asg.AgentState = '3' then 'NotReady'
when asg.AgentState = '3' then 'Ready'
else 'unknownState'
end) as AgentState
from Agent_Skill_Group_Real_Time asg
where SkillGroupSkillTargetID in (Select SkillTargetID from Skill_Group where EnterpriseName in ('put your skill here'))

View solution in original post

12 REPLIES 12
Chintan Gajjar
Collaborator

sample Query:

 

Select (Select EnterpriseName from Agent a where a.SkillTargetID = asg.SkillTargetID) as AgentName,
(case when asg.AgentState = '0' then 'LoggedOff'
when asg.AgentState = '1' then 'LoggedOn'
when asg.AgentState = '3' then 'NotReady'
when asg.AgentState = '3' then 'Ready'
else 'unknownState'
end) as AgentState
from Agent_Skill_Group_Real_Time asg
where SkillGroupSkillTargetID in (Select SkillTargetID from Skill_Group where EnterpriseName in ('put Skill Group Name' here ))

 

please refer to db schema guide for the table Agent_Skill_Group_RealTime and define the agentstate accordingly.

you should run the query on realtime db(awdb) and you will get the results.

 

 

That's perfect thank you, works just as I need it too.  If I wanted to add in the agent name and agent ID, is that possible?

you want add that in results or you want give it as input?

Id like to be able to see the agent names in the same output? Is that possible?

yes, try this

Select 
(Select EnterpriseName from Agent a where a.SkillTargetID = asg.SkillTargetID) as AgentName,
(Select FirstName from Person p where p.PersonID = (Select PersonID from Agent a where a.SkillTargetID = asg.SkillTargetID)) as Firstname,
(Select LastName from Person p where p.PersonID = (Select PersonID from Agent a where a.SkillTargetID = asg.SkillTargetID)) as Lastname,
(case when asg.AgentState = '0' then 'LoggedOff'
when asg.AgentState = '1' then 'LoggedOn'
when asg.AgentState = '3' then 'NotReady'
when asg.AgentState = '3' then 'Ready'
else 'unknownState'
end) as AgentState
from Agent_Skill_Group_Real_Time asg
where SkillGroupSkillTargetID in (Select SkillTargetID from Skill_Group where EnterpriseName in ('put your skill here'))

View solution in original post

That's brilliant! Thank you, this will help me very much!

Select TOP 100
(Select EnterpriseName from Agent a where a.SkillTargetID = asg.SkillTargetID) as AgentName,
(Select FirstName from Person p where p.PersonID = (Select PersonID from Agent a where a.SkillTargetID = asg.SkillTargetID)) as Firstname,
(Select LastName from Person p where p.PersonID = (Select PersonID from Agent a where a.SkillTargetID = asg.SkillTargetID)) as Lastname,
(Select p.LoginName from Person p where p.PersonID = (Select PersonID from Agent a where a.SkillTargetID = asg.SkillTargetID)) as LoginName,
(case when asg.AgentState = '0' then 'LoggedOff'
when asg.AgentState = '1' then 'LoggedOn'
when asg.AgentState = '2' then 'NotReady'
when asg.AgentState = '3' then 'Ready'
when asg.AgentState = '4' then 'Talking'
when asg.AgentState = '5' then 'Work Not Ready'
when asg.AgentState = '6' then 'Work Ready'
when asg.AgentState = '7' then 'Busy Other'
when asg.AgentState = '8' then 'Reserved'
when asg.AgentState = '9' then 'Unknown'
when asg.AgentState = '10' then 'Calls On Hold'
when asg.AgentState = '11' then 'Active'
when asg.AgentState = '12' then 'Paused'
when asg.AgentState = '13' then 'Interrupted'
else 'Not Specified'
end) as AgentState
from Agent_Skill_Group_Real_Time asg
where SkillGroupSkillTargetID in (Select SkillTargetID from Skill_Group where EnterpriseName in ('Your skill group name here'))


Select TOP 100
(Select EnterpriseName from Agent a where a.SkillTargetID = asg.SkillTargetID) as AgentName,
(Select FirstName from Person p where p.PersonID = (Select PersonID from Agent a where a.SkillTargetID = asg.SkillTargetID)) as Firstname,
(Select LastName from Person p where p.PersonID = (Select PersonID from Agent a where a.SkillTargetID = asg.SkillTargetID)) as Lastname,
(Select p.LoginName from Person p where p.PersonID = (Select PersonID from Agent a where a.SkillTargetID = asg.SkillTargetID)) as LoginName,
(case when asg.AgentState = '0' then 'LoggedOff'
when asg.AgentState = '1' then 'LoggedOn'
when asg.AgentState = '2' then 'NotReady'
when asg.AgentState = '3' then 'Ready'
when asg.AgentState = '4' then 'Talking'
when asg.AgentState = '5' then 'Work Not Ready'
when asg.AgentState = '6' then 'Work Ready'
when asg.AgentState = '7' then 'Busy Other'
when asg.AgentState = '8' then 'Reserved'
when asg.AgentState = '9' then 'Unknown'
when asg.AgentState = '10' then 'Calls On Hold'
when asg.AgentState = '11' then 'Active'
when asg.AgentState = '12' then 'Paused'
when asg.AgentState = '13' then 'Interrupted'
else 'Not Specified'
end) as AgentState, Agent.SkillTargetID, Agent.PeripheralNumber, Agent.EnterpriseName, Agent.PeripheralName
from Agent_Skill_Group_Real_Time asg
inner join Agent on Agent.SkillTargetID = asg.SkillTargetID
where SkillGroupSkillTargetID in (Select SkillTargetID from Skill_Group where EnterpriseName in ('Your skill group name here'))

if I have the Agent Team Name, how do I pull the Skill Groups they are on and also what script they are configured? Can you help with the SQL? 

Yes, I saw it, perfect. Thanks again.
pchaudhary2000
Beginner

Select TOP 100
(Select EnterpriseName from Agent a where a.SkillTargetID = asg.SkillTargetID) as AgentName,
(Select FirstName from Person p where p.PersonID = (Select PersonID from Agent a where a.SkillTargetID = asg.SkillTargetID)) as Firstname,
(Select LastName from Person p where p.PersonID = (Select PersonID from Agent a where a.SkillTargetID = asg.SkillTargetID)) as Lastname,
(Select p.LoginName from Person p where p.PersonID = (Select PersonID from Agent a where a.SkillTargetID = asg.SkillTargetID)) as LoginName,
(case when asg.AgentState = '0' then 'LoggedOff'
when asg.AgentState = '1' then 'LoggedOn'
when asg.AgentState = '2' then 'NotReady'
when asg.AgentState = '3' then 'Ready'
when asg.AgentState = '4' then 'Talking'
when asg.AgentState = '5' then 'Work Not Ready'
when asg.AgentState = '6' then 'Work Ready'
when asg.AgentState = '7' then 'Busy Other'
when asg.AgentState = '8' then 'Reserved'
when asg.AgentState = '9' then 'Unknown'
when asg.AgentState = '10' then 'Calls On Hold'
when asg.AgentState = '11' then 'Active'
when asg.AgentState = '12' then 'Paused'
when asg.AgentState = '13' then 'Interrupted'
else 'Not Specified'
end) as AgentState
from Agent_Skill_Group_Real_Time asg
where SkillGroupSkillTargetID in (Select SkillTargetID from Skill_Group where EnterpriseName in ('Your skill group name here'))

Content for Community-Ad

Spotlight Awards 2021