AgentTeamSkillID - Which table and column?

Version 1
    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