Active TopicsMemberlistSearchHelpRegisterLogin

Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: Help Linking Tables Post Reply Post New Topic
Author Message
FrnhtGLI
Senior Member
Senior Member
Avatar

Joined: 22 May 2009
Online Status: Offline
Posts: 347
Quote FrnhtGLI Replybullet Topic: Help Linking Tables
     Posted: 09 Dec 2011 at 8:43am
I don't really have much experience linking tables so I'm really confused right now.

Report Overview
I am working with information from a call center and am trying to create a report that shows only those calls that last over one hour. The information is coming from 4 tables; Resource, Team, ContactCallDetail, and AgentConnectionDetail.


Table Overview
  Resource - needed to show the agent's name and extension and used to group by agent name. Can be mapped to Team table, ContactCallDetail table and AgentConnectionDetail table.

  Team - needed to group by team name. Can be mapped to Resource table only.

  ContactCallDetail - needed to show start time and end time of outgoing calls, the number dialed, and has a field that shows the call connect time (how long the call was connected to the agent in seconds). Can be mapped to Resource table and AgentConnectionDetail table.

  AgentConnectionDetail - needed to show start time and end time of incoming calls and to calculate the time connected to those calls. Can be mapped to Resource table and ContactCallDetail table.


Grouping
First group - Team Name (Team table)
Second Group - Resource Name (Resource table)
ISSUE>>>Third Group - ?? (Has to be by the call start time for each second, but not sure if I need to use the call start time of ContactCallDetail table or AgentConnectionDetail table or both somehow)


Main Issue
ISSUE>>>I need to show both the incoming and outgoing calls grouped by earliest to latest call start time. I'm not sure how the ContactCallDetail table and AgentConnectionDetail need to be linked or which type of link needs to be used in order to achieve this .

ISSUE>>>The ContactCallDetail table contains information that needs to be used concerning incoming call times stored in the AgentConnectionDetail table (such as the call origin number). I believe this is related to the linking issue as well.

Okay, so the main thing I need help with is how the tables need to be linked so I can show records from both the ContactCallDetail table and AgentConnectionDetail table.

Any help would be appreciated.

Thanks in advance.
|< /\ '][' ( )
IP IP Logged
FrnhtGLI
Senior Member
Senior Member
Avatar

Joined: 22 May 2009
Online Status: Offline
Posts: 347
Quote FrnhtGLI Replybullet Posted: 12 Dec 2011 at 2:12am
Okay, so I figured out that in order to have both fields from the two separate tables I need to use the UNION function as a COMMAND. Here is what I have and it joins the two like fields as I need:

SELECT
"ContactCallDetail"."startDateTime", "ContactCallDetail"."endDateTime"
FROM
"PhoneSystemData"."dbo"."ContactCallDetail"
UNION
SELECT
"AgentConnectionDetail"."startDateTime", "AgentConnectionDetail"."endDateTime"
FROM
"PhoneSystemData"."dbo"."AgentConnectionDetail"

Now my problem is that I need to add other fields from different tables to the report on top of the ones in the COMMAND line above. Does anyone know how I would create the SQL statement for this?

Any help at all is greatly appreciated.

Thanks!
|< /\ '][' ( )
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 12 Dec 2011 at 3:59am
you might rethink the union option as it sounds that the ContactCallDetail and AgentConnectionDetail are related and may actually be overlapping data. I am guessing that the contact call data is 'master' record and the agent data is sub parts of the contact that shows if a contact was transferred to multiple agents during one contact. Is this accurate?
If so, one question is can there be an agent row of data that has no corresponding contact row or vice versa? If not, your union porcess is not needed and will likely confuse your results.
IP IP Logged
FrnhtGLI
Senior Member
Senior Member
Avatar

Joined: 22 May 2009
Online Status: Offline
Posts: 347
Quote FrnhtGLI Replybullet Posted: 12 Dec 2011 at 4:26am
The two fields from both the AgentConnectionDetail and ContactCallDetail do not contain any of the same times. One deals with incoming calls and the other deals with outgoing calls. I have confirmed this by running a report using each table separately.

If an agent did not receive a call that agent will not show in the database. All the records are basically blank until an agent receives a call instead of all the agents being in the database and it recording when they get a call. If the agent did not get a call, it is as if (according to the way the database is) the agent doesn't even work here. There will be no record whatsoever of that agent.
|< /\ '][' ( )
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 12 Dec 2011 at 4:45am
in that case the union may work.
you should include in it the other fields you need to link the calls to the agent or team.
once you have them included int he union you can add those tables to the report and link as you normally would or you can the tables straight into the command and link there (in sql)
IP IP Logged
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum

HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | PRIVACY | CLICK PLAYS | KONACAD

© 2004-2026 Brian Bischof. All Rights Reserved.