I have a sql query for agent name, PQ name and attribute and another sql query to obtain dial number, call type and script but I need to some how link to two together. However I can not locate a link between the two in the tables. From memory someone told because we use PQ?
I've looked through the data scheme pdf but that didn't give any ideas - I'm not a sql person
I need the following in a single query but unable to join, link the above two script ls which separately seem to provide the two half's of the equation
Dial number, call type, script, attribute, agent first name, agent last name, attribute value
Ok.. so you want to get Agent details along with Agent attribute value, DN, Script name(which was invoked by DN) and Call type(can be differently set, for example, there is a call type used only to schedule ICM script with DN and not used in ICM script, and another call type is used just before PQ to count number of calls handled for that PQ) for a particular call or set of calls ?
so dialled number, call type ,script, agent , attribute, attribute value, PQ
We assign unique call type to dialled numbers so one or more dialler number may be assigned to a single script but each number can be identified via it’s own call type
i don’t seem to be able to see a link between master script , dialled number and say PQ or Attribute. Something to link scripts /dialler number to agent /PQ
Ok.. so first table to look would be TCD or RCD for call detail and that will give you agent, PQ, attribute, CT and DN details. Now based on these details, need to join other tables to get Enterprise name and script and call type of DN.
It seems quite challenging to gather all these tables, meanwhile you had mentioned in first post that you have two halves of the query; can you share that if you dont mind. it can be a good starting point.
Please check below query and see if it provides relevant details to you in terms of IDs in most cases. If it is providing you necessary details, I will join other tables to add respective enterprise names, for example, for PQ, attribute, agent, script name etc.
select top 10 tcd.DateTime,tcd.AgentSkillTargetID, tcd.AgentPeripheralNumber,tcd.DigitsDialed,tcd.PrecisionQueueID,tcd.Attributes,tcd.CallTypeID,rcd.ScriptID
from Termination_Call_Detail (nolock) tcd, Route_Call_Detail (nolock) rcd
tcd.RouterCallKeyDay = rcd.RouterCallKeyDay and
tcd.RouterCallKey = rcd.RouterCallKey and
tcd.DateTime between '2019-09-24 08:00:00' and '2019-09-24 08:15:00' and
tcd.PrecisionQueueID is not NULL
order by tcd.DateTime desc