AgentTeamSkillID - Which table and column?

Document created by cdnadmin on Jan 25, 2014
Version 1Show Document
  • View in full screen mode
This document was generated from CDN thread

Created by: Erik Johansson on 28-06-2012 02:06:38 AM
Hi,
 
I need some help finding the correct table and column of the four digit ID of Agent Teams. I´ve searched all related database schemes but I can´t find it anywhere.
 
SQL from the Agent Team Historical All Fields report: (AgentSkillGroup.AgentTeamSkillID IN (5031))
 
Now, I need to make a SQL Query referring to the table and column that lists all four digit ID´s of all teams.
The query should look something like: SELECT DISTINCT "column" FROM "table" ORDER BY "column"
 
I would really appreciate some guidance.
 
Thank you!

Subject: RE: AgentTeamSkillID - Which table and column?
Replied by: Erik Johansson on 28-06-2012 08:48:02 AM
Hi Stephan,

Thank you for your reply.

Unfortunately, it doesn´t solve the problem.

When running that query, the log file outputs: 06/28/12 14:05:01 > Error = [ODBC SQL Server Driver][SQL Server]Invalid column name 'AgentTeamID'.
But there is a column named AgentTeamID, so that message is quite weird..

In the interface I´m using, I have specified the following:

CallDataTable=Agent_Skill_Group_Half_Hour
QueueSQL=SELECT DISTINCT AgentTeamID FROM Agent_Team_Member ORDER BY AgentTeamID
QueueNameColumn=AgentTeamID

Maybe the QueueNameColumn tries to read from the CallDataTable instead of the Agent_Team_Member table?


//Erik

Subject: RE: AgentTeamSkillID - Which table and column?
Replied by: Stephan Hartman on 28-06-2012 09:00:41 AM
Hi Erik,
There is no feild labled AgentTeamID in the Agent_Skill_Group_Half_Hour table so that is not possible.

If you run the query SELECT DISTINCT AgentTeamID FROM Agent_Team_Member ORDER BY AgentTeamID against the AWDB in the SQL Mgmt Studio, does it fail?
Can you use CallDataTable=Agent_Team_Member in your script?

Steve

Subject: RE: AgentTeamSkillID - Which table and column?
Replied by: Luis Yrigoyen on 28-06-2012 09:37:32 AM
Erik,
What are you trying to accomplish? all you want is the 4-digit team ID? or is this to build a report to show which team an agent belongs to?

if all you want is the 4-digit ID for a team then that table is the Agent_Team table in the AWDB but if you want to show which team an agent belongs to you would link two or three tables (three if you want to show the agent's full name)

A query like this one will give you the agent's full name and the name of the team:

select
AgentName = (PERSON.FirstName + ' ' + PERSON.LastName),
AGENT.EnterpriseName as AgentLoginName,
--MEMBERSHIP.AgentTeamID,
TEAMS.EnterpriseName as Team
from
t_Agent AGENT
left join
t_Person PERSON
on
AGENT.PersonID = PERSON.PersonID
left join
t_Agent_Team_Member MEMBERSHIP
on
AGENT.SkillTargetID = MEMBERSHIP.SkillTargetID
left join
t_Agent_Team TEAMS
on
MEMBERSHIP.AgentTeamID = TEAMS.AgentTeamID

order by
TEAMS.EnterpriseName

Subject: RE: AgentTeamSkillID - Which table and column?
Replied by: Stephan Hartman on 28-06-2012 08:32:22 AM
Hi,
 
I need some help finding the correct table and column of the four digit ID of Agent Teams. I´ve searched all related database schemes but I can´t find it anywhere.
 
SQL from the Agent Team Historical All Fields report: (AgentSkillGroup.AgentTeamSkillID IN (5031))
 
Now, I need to make a SQL Query referring to the table and column that lists all four digit ID´s of all teams.
The query should look something like: SELECT DISTINCT "column" FROM "table" ORDER BY "column"
 
I would really appreciate some guidance.
 
Thank you!


 
Hi Eric,
The following SQL Query should do the trick:
<font size="2" color="#0000ff"><font size="2" color="#0000ff">
SELECT
</font></font><font size="2" color="#0000ff">
 
</font>
 
DISTINCT AgentTeamID
FROM Agent_Team_Member
ORDER BY AgentTeamID

Let me know if this works for you.

Steve

Subject: RE: AgentTeamSkillID - Which table and column?
Replied by: Stephan Hartman on 28-06-2012 08:39:10 AM
Hi Eric,
The following SQL Query should do the trick:

SELECT DISTINCT AgentTeamID
FROM Agent_Team_Member
ORDER BY AgentTeamID

Run this against the AWDB.
Let me know if this works for you.

Steve

Subject: RE: AgentTeamSkillID - Which table and column?
Replied by: Stephan Hartman on 28-06-2012 10:31:27 AM
hi Erik,

here is the previous post with an added field for the Agent Team 4 digit ID number.

select Distinct
AgentName = (PERSON.FirstName + ' ' + PERSON.LastName),
AGENT.EnterpriseName as AgentLoginName,
TEAMS.EnterpriseName as Team,
TEAMS.AgentTeamID as TeamID
from t_Agent AGENT
left join t_Person PERSON
on AGENT.PersonID = PERSON.PersonID
left join t_Agent_Team_Member MEMBERSHIP
on AGENT.SkillTargetID = MEMBERSHIP.SkillTargetID
left join t_Agent_Team TEAMS
on MEMBERSHIP.AgentTeamID = TEAMS.AgentTeamID
left join Agent_Team_Member TEAMID
on MEMBERSHIP.AgentTeamID = TEAMID.AgentTeamID
order by TEAMS.EnterpriseName

Steve

Subject: RE: AgentTeamSkillID - Which table and column?
Replied by: Erik Johansson on 29-06-2012 12:51:04 AM
Thank you for all your help!

I have now been able to get help from our Local IT to run some queries towards Agent_Team.

I think the problem is the interface that I´m using. All the four digit ID´s are shown in the column AgentTeamID, exactly as they are supposed to, so I should be able to retrieve them from the table. At least I now know that the table and column isn´t the problem. I´ll continue searching with the vendor of the interface.

Thanks again, great answers!

//Erik

Attachments

    Outcomes