UCCE 12.x SQL query showing Agent, Agent Team, and Agent Skills

[Mindriot]

Would appreciate some assistance with putting together a SQL query, since I am only a 'plug and play' user, that will show/query Team Name, Agent Name, and Agent skills.  Supervisors are complaining that they are seeing Queue data or queue statistics for skills/queues that are not relevant to their team.  This is occurring since many agents in that team always remain in a not ready status so they are not aware if/when they are mis-assigned a skill.  RT data is not necessary, just user database tables would work.  Any help would be appreciated.  TIA!

Running UCCE 12.x in case that has any bearing on the DB schema...

[Omar Deen]

SELECT P.FirstName,
       P.LastName,
       P.LoginName,
       A.PeripheralNumber,
       A.SupervisorAgent,
       SG.PeripheralName AS SkillGroup,
       AT.EnterpriseName AS AgentTeam
FROM Agent A,
     Person P,
     Skill_Group SG,
     Skill_Group_Member SGM,
     Agent_Team_Member ATM,
     Agent_Team AT
WHERE A.PersonID = P.PersonID
  AND (A.Deleted = 'N')
  AND A.SkillTargetID = SGM.AgentSkillTargetID
  AND SGM.SkillGroupSkillTargetID = SG.SkillTargetID
  AND A.SkillTargetID = ATM.SkillTargetID
  AND ATM.AgentTeamID = AT.AgentTeamID
ORDER BY P.LastName

[Omar Deen]

Here's one for PQs

SELECT P.FirstName, P.LastName, A.EnterpriseName, AGT.EnterpriseName AS AgentTeam, AT.AttributeValue, ATT.EnterpriseName
FROM Agent A, Agent_Attribute AT, Attribute ATT, Person P, Agent_Team AGT, Agent_Team_Member ATM
WHERE A.SkillTargetID = AT.SkillTargetID
AND ATT.AttributeID = AT.AttributeID
AND A.PersonID = P.PersonID
AND A.SkillTargetID = ATM.SkillTargetID
AND ATM.AgentTeamID = AGT.AgentTeamID

[MohankumarSubramanian12377]

Hi Omar,

Thanks for your above query. If suppose i need to collect  all  agent ID and their  associated Agent Team . could you pls help me in it. 

[Muhammed Ashiq]

Is this what you looking for ?

select a.PeripheralNumber,at.EnterpriseName

from Agent a,Agent_Team at,Agent_Team_Member atm

where atm.SkillTargetID=a.SkillTargetID

and atm.AgentTeamID=at.AgentTeamID