Query Skill Groups for Agent Team

[abhilash2001]

If I know an Agent Team Name, how can I run an SQL query to pull all the Skill Groups for those agents within that Team? 

[david.macias]

If I make time for this I can crank out the query, but here are the different views you need to join.

Agent_Team, Agent_Team_Member, Agent, Skill_Group, and Skill_Group_Member.

david

[abhilash2001]

Thanks David, let me know if you can find some time to construct the query.

[bill.king1]

Abhilash2001, did you search the previous posts?

Isn't this what you want?

https://community.cisco.com/t5/contact-center/export-skill-group-members-list-for-ucce-8-5/td-p/2131524

[abhilash2001]

No this is not what I was looking for. I only have Agent Team name, I need a query to pull the skill group using the Agent Team Name. 

[piyush aghera · Accepted solution]

Same query which Bill mentioned, can be altered a bit as below to get what you want.

SELECT
agm.AgentTeamID, ag.*, sg.*
FROM Skill_Group_Member sgm
LEFT OUTER JOIN Agent ag ON sgm.AgentSkillTargetID = ag.SkillTargetID
LEFT OUTER JOIN Skill_Group sg ON sgm.SkillGroupSkillTargetID = sg.SkillTargetID
left outer join Agent_Team_Member agm on ag.SkillTargetID = agm.SkillTargetID
where agm.AgentTeamID = 5002

This query can be beautified to include what you want and names instead of IDs.  however if you are going to use as Administrator of the environment, you may not need to change lot of things here.

[abhilash2001]

Perfect, thank you very much Piyush. 

[piyush aghera]

Welcome :-)

Please rate useful posts.

[abhilash2001]

Hi Piyush, can you help me with one more item? I am trying to pull a query of list of all Agents from all Agent Teams who has logged in recently. I have the two below queries for the two different functions, somehow we need to combine them to pull my query. 

select *

FROM Agent_Team, Agent_Team_Member, Skill_Group_Member

INNER JOIN Agent ON Skill_Group_Member.AgentSkillTargetID = Agent.SkillTargetID
INNER JOIN Person ON Agent.PersonID = Person.PersonID

WHERE Agent.SkillTargetID = Agent_Team_Member.SkillTargetID

AND Agent_Team_Member.AgentTeamID = Agent_Team.AgentTeamID

order by Agent_Team.AgentTeamID

----------------------------------------------

SELECT DISTINCT P.FirstName, P.LastName, P.LoginName, MAX(AED.LoginDateTime) AS LastLoginTime
FROM Agent A, Agent_Event_Detail AED, Person P
WHERE A.SkillTargetID = AED.SkillTargetID
AND A.PersonID = P.PersonID
GROUP BY P.FirstName, P.LastName, P.LoginName
ORDER BY LastLoginTime ASC

[piyush aghera]

I think the best way to do this is to store results of one of the query to a temp table and then perform inner join with results of the other query to find out common data between two tables, in this case agents who have recently logged in.

[piyush aghera]

I looked at both the queries now and i think your second query itself is enough to find out all the agents who have recently logged in by looking at LastLoginTime.

Do you want to add Agent Team name also in this query ?

[abhilash2001]

Yes, can we add the Agent Team in that query? 

[piyush aghera · Accepted solution]

Here we go:

SELECT DISTINCT P.FirstName, P.LastName, P.LoginName, MAX(AED.LoginDateTime) AS LastLoginTime, Agent_Team_Member.AgentTeamID, Agent_Team.EnterpriseName
FROM Agent A, Agent_Event_Detail AED, Person P, Agent_Team_Member, Agent_Team
WHERE A.SkillTargetID = AED.SkillTargetID
and Agent_Team_Member.AgentTeamID = Agent_Team.AgentTeamID
and A.SkillTargetID = Agent_Team_Member.SkillTargetID
AND A.PersonID = P.PersonID
GROUP BY P.FirstName, P.LastName, P.LoginName, Agent_Team_Member.AgentTeamID, Agent_Team.EnterpriseName
ORDER BY LastLoginTime desc