linking symposium tables

[vincent5]

Hi,

I am trying trough crystal report to link the dApplicationStat and the d AgemtbyApplicationStat tables.

The nortel database reference manual does explain which keys to link, but I can't seem to be able to get the result I need.

I am trying to match the "calls answered" field.. but one table lists it by application and the other one by agent.
Is there a formula I should write to match one and the other.

Do you know of any forum that discusses the linking of symposium tables ?

Thanks in advance

 

[aperez54]

The dapplicationstat table is a summary of all calls by day by application.

The dAgentApplicationstat table is a summary of all calls by day by application by AGENT.

In this case, you will first need to create a query to sum all the calls from dagentapplicationstat by day. Do a grouping on timestamp and applicationID (or application name) then do your totals on this grouping.

The totals from this query should then match to your dapplicationstat table. You can use this query to link by timestamp and applicationID (or application name whichever you chose but do the ID instead since I assume since it's a number it's most likely indexed and query would work faster).

Hope this helps.

 

[aperez54]

Here is a quick SQL on the dagentapplicationstat, sum on calls answered

SELECT dbo_dAgentByApplicationStat.Timestamp, dbo_dAgentByApplicationStat.ApplicationID, dbo_dAgentByApplicationStat.Application,
Sum(dbo_dAgentByApplicationStat.CallsAnswered) AS SumOfCallsAnswered
FROM dbo_dAgentByApplicationStat
GROUP BY dbo_dAgentByApplicationStat.Timestamp, dbo_dAgentByApplicationStat.ApplicationID, dbo_dAgentByApplicationStat.Application;

I named that query as "CallsAnswered_a". Using this query, I created another query joining it to dapplicationstat:

SELECT dbo_dApplicationStat.Timestamp, dbo_dApplicationStat.ApplicationID, dbo_dApplicationStat.Application, Sum(dbo_dApplicationStat.CallsAnswered) AS dApplStatCallsAnswered,
Sum(CallsAnswered_a.SumOfCallsAnswered) AS dAgentStatCallsAnswered
FROM dbo_dApplicationStat INNER JOIN CallsAnswered_a ON (dbo_dApplicationStat.ApplicationID = CallsAnswered_a.ApplicationID) AND (dbo_dApplicationStat.Timestamp = CallsAnswered_a.Timestamp)
GROUP BY dbo_dApplicationStat.Timestamp, dbo_dApplicationStat.ApplicationID, dbo_dApplicationStat.Application;


hope that helps.