Hi Geeks
This is my first post and i hope to recieve a relevent answers from the experts here
we have recently transition from quaters to tertials and there is a job which counts all the calls made by rep every qyater (Calls QTD).
this has to change now to tertial but there is no standard function in oracle for tertial.
statement that needs to be changed here is AND T2.APPT_START_DT >= to_date(trunc(sysdate,'Q')) (Query Below)
Please help!!
T1.PERSON_UID,
PT1.NAME POSTN_NAME,
G1.X_AZ_LAST_CALL_DT,
G2.X_AZ_CALLS_QTD,
G3.X_MY_CALLS_QTD
FROM SIEBEL.S_CONTACT T1,
SIEBEL.S_POSTN_CON C1,
SIEBEL.S_POSTN PT1,
SIEBEL.S_CONTACT E1,
( SELECT /*+ INDEX(T2 S_EVT_ACT_CALL_X) */
T2.TARGET_PER_ID,
MAX(APPT_START_DT)
as X_AZ_LAST_CALL_DT
FROM SIEBEL.S_EVT_ACT T2
WHERE T2.SUBTYPE_CD ='Professional Call'
AND T2.EVT_STAT_CD ='Submitted'
AND T2.APPT_START_DT >= to_date(trunc(sysdate,'Q'))
GROUP BY T2.TARGET_PER_ID ) G1,
(SELECT /*+ INDEX(T2 S_EVT_ACT_CALL_X1) */
T2.TARGET_PER_ID, COUNT(T2.ROW_ID) as X_AZ_CALLS_QTD
FROM SIEBEL.S_EVT_ACT T2
WHERE T2.SUBTYPE_CD ='Professional Call'
AND T2.EVT_STAT_CD ='Submitted'
AND T2.APPT_START_DT >= to_date(trunc(sysdate,'Q'))
AND T2.TODO_CD ='1-1 Call'
GROUP BY T2.TARGET_PER_ID) G2,
(SELECT /*+ INDEX(T2 S_EVT_ACT_CALL_X) */ T2.TARGET_PER_ID,
T3.EMP_ID, COUNT(T3.ROW_ID) AS X_MY_CALLS_QTD
FROM SIEBEL.S_EVT_ACT T2, SIEBEL.S_ACT_EMP T3
WHERE T2.ROW_ID = T3.ACTIVITY_ID
AND T2.SUBTYPE_CD ='Professional Call'
AND T2.EVT_STAT_CD ='Submitted'
AND T2.APPT_START_DT >= to_date(trunc(sysdate,'Q'))
AND T2.TODO_CD ='1-1 Call'
GROUP BY T2.TARGET_PER_ID,T3.EMP_ID) G3
WHERE T1.ROW_ID = C1.CON_ID
AND G3.EMP_ID = E1.ROW_ID
AND E1.PR_HELD_POSTN_ID = PT1.ROW_ID
AND PT1.ROW_ID = C1.POSTN_ID
AND T1.ROW_ID = G1.TARGET_PER_ID
AND T1.ROW_ID = G2.TARGET_PER_ID
AND T1.ROW_ID = G3.TARGET_PER_ID
Reply With Quote
Originally Posted by debasisdas
