Eliminating duplicates in crystal reports formula field

[reacha]

i have written a query to build the report and in the query i am using two tables called calldetail and ret and i am doing outer join on two tables using callid so that all the records for ret will be coming and the matching from calldetail but i am getting duplicate callids in the report and am grouping by agentid

handled = count(command.callid,command.agentid)
talktime = sum(command.agenttalktime,command.agentid)

i am do distinctcount on handled but talktime is getting wrong for duplicates the talktime is summing up twice

Please help me out!!

 

[FireGeek21]

Please include your query if possible.

Thanks

RSGeek


RSGeek
(currently using Crystal Reports XI with Lawson 8.03)

 

[reacha]

SELECT * FROM
(SELECT *
FROM RET
WHERE TRUNC(CALLSTART) >= :CALLSTART AND TRUNC(CALLSTART) <= :CALLEND
AND campaign_id IN ('TWCHRLTRT','TWCOLOM','TWGRBO','TWPORT','TWRAL01','TWWIL01'))T1,
(select TOTAL_POINTS ,CALL_ID,AGENT_ID,DIVISION
from table1
WHERE TRUNC(CALL_DATE) = :CALLSTART AND TRUNC(CALL_DATE) = :CALLEND)T2,
(SELECT agentid,callid,call_acw,agenttalktime,COUNT(callid),SUM(agenttalktime+call_acw)
FROM calldetail
WHERE TRUNC(callstarttime) >= :CALLSTART AND TRUNC(callstarttime)< = :CALLEND
AND campaignid IN ('TWCHRLTRT','TWCOLOM','TWGRBO','TWPORT','TWRAL01','TWWIL01')
AND gt_shortcall = 0 AND abandon = 0
GROUP BY agentid,callid,call_acw,agenttalktime)T3,
(select AGT_FULL_NAME,AGENTID,hire_dt
FROM table2 )T4
WHERE T1.callid(+) = T3.callid
and T1.AGENT_ID (+)= T3.AGENTID
and T1.AGENT_ID = T4.AGENTID(+)
and TO_CHAR(T3.CALLID) = T2.CALL_ID(+)
--and T4.agentid(+) = T3.agentid


I thought it would be very complex if i tell that i am joining 4 tables.I have used table1 and table2 because i need total_points from table1 and table2 because i need agt_full_name and hire_dt

Please do advise!!

 

[reacha]

I think the previous query was little confusing and this is the modified query in which i joined just two tables

SELECT * FROM
(SELECT *
FROM RET
WHERE TRUNC(CALLSTART) >= :CALLSTART AND TRUNC(CALLSTART) <= :CALLEND
AND campaign_id IN ('TWCHRLTRT','TWCOLOM','TWGRBO','TWRAL01','TWWIL01'))T1,
(SELECT agentid,callid,call_acw,agenttalktime,COUNT(callid),SUM(agenttalktime+call_acw)
FROM calldetail
WHERE TRUNC(callstarttime) >= :CALLSTART} AND TRUNC(callstarttime)< = :CALLEND
AND campaignid IN ('TWCHRLTRT','TWCOLOM','TWGRBO','TWRAL01','TWWIL01')
AND gt_shortcall = 0 AND abandon = 0
GROUP BY agentid,callid,call_acw,agenttalktime)T3
WHERE T1.callid(+) = T3.callid


In this i need to get all the data from calldetail and matching data from ret.

My problem is that in the ret table there are some duplicate callid's which i need to avoid.

Please help me out!!

Thanks,
reacha

 

[Madawc]

Crystal detail lines are composed of all the detail records that are linked to each other. This often means duplicates. The normal solution is to group on some suitable field, callid in your case. Suppress the detail lines and show the stuff you need in the group header or footer.

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP