SELECT 
b.DATA_TYPE,b.TIME_DATE,NVL(b.CS_ID,0) CS_ID,NVL(b.CALLOUT_NUM,0) CALLOUT_NUM,NVL(b.CALLOUT_ANSWER_NUM,0) CALLOUT_ANSWER_NUM,NVL(b.CALLOUT_DURATION,0) CALLOUT_DURATION,NVL(CALLOUT_DURATION/NULLIF(CALLOUT_ANSWER_NUM,0),0) AVG_TIME
FROM (
SELECT
	'2' AS DATA_TYPE,
	TO_CHAR(TIME_DATE, 'YYYY-MM-DD') TIME_DATE,
	CS_ID,
	sum(CALLOUT_NUM) CALLOUT_NUM,
	sum(CALLOUT_ANSWER_NUM) CALLOUT_ANSWER_NUM,
	sum(CALLOUT_DURATION)  CALLOUT_DURATION
FROM (SELECT TIME_DATE,CS_ID,ORG_ID,CALLOUT_NUM,CALLOUT_ANSWER_NUM,CALLOUT_DURATION,RECALL_TYPE_ID,
		CASE WHEN OUTBOUND_TYPE_ID = 1 THEN 0 WHEN OUTBOUND_TYPE_ID = 2 THEN 1 END AS OUTBOUND_TYPE_ID 
		FROM T_LI_RPT_CALLOUT_OPERATION  ) a 
WHERE TIME_DATE >= TRUNC(DATE'${P_START}')
	  AND TIME_DATE <= TRUNC(DATE'${P_END}')
	  ${if(len(P_CS)==0,"","and CS_ID = ("+P_CS+")")}
 	  ${if(len(P_CALLOUT)==0,"","and OUTBOUND_TYPE_ID = ("+P_CALLOUT+")")}
 	  ${if(len(P_RECALL)==0,"","and RECALL_TYPE_ID = ("+P_RECALL+")")}
 	  ${if(len(p_org2)==0,"","and ORG_ID in ("+p_org2+")")} 	  
GROUP by     
	TIME_DATE,
	CS_ID
UNION ALL 	
select 
	'1' AS DATA_TYPE,
	TO_CHAR(TIME_DATE, 'YYYY-MM-DD') TIME_DATE,
	CS_ID,
	sum(CALLOUT_NUM) CALLOUT_NUM,
	sum(CALLOUT_ANSWER_NUM) CALLOUT_ANSWER_NUM,
	sum(CALLOUT_ANSWER_TIME)  CALLOUT_ANSWER_TIME
FROM T_LI_RPT_CALLOUT 
WHERE TIME_DATE >= TRUNC(DATE'${P_START}')
	  AND TIME_DATE <= TRUNC(DATE'${P_END}')
	  ${if(len(P_CS)==0,"","and CS_ID = ("+P_CS+")")}
 	  ${if(len(P_CALLOUT)==0,"","and OUTBOUND_TYPE_ID in ("+P_CALLOUT+")")}
GROUP by     
	TIME_DATE,
	CS_ID 
ORDER BY TIME_DATE) b
WHERE 1 = 1
${if(len(P_DATA)==0,"","and B.DATA_TYPE = ("+P_DATA+")")}	

