cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
6412
Views
5
Helpful
12
Replies

Query Skill Groups for Agent Team

abhilash2001
Level 1
Level 1

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? 

2 Accepted Solutions

Accepted Solutions

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.

View solution in original post

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

View solution in original post

12 Replies 12

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

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

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. 

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.

Perfect, thank you very much Piyush. 

Welcome :-)

 

Please rate useful posts.

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

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.

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 ?

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

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