Query for tertial (4 month)

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

What is the problem with this query and what exactly you are trying to do ?

Try using the following method
select case when to_char(trunc(sysdate),'mm') between '01' and '04' then '01-01-'||to_char(sysdate,'yyyy')
when to_char(trunc(sysdate),'mm') between '05' and '08' then '01-05-'||to_char(sysdate,'yyyy')
when to_char(trunc(sysdate),'mm') between '09' and '12' then '01-09-'||to_char(sysdate,'yyyy') end case
from dual

[QUOTE=debasisdas;28538]What is the problem with this query and what exactly you are trying to do ?[/QUOTE]

Hi Ya

the problem is we cant use T2.APPT_START_DT >= to_date(trunc(sysdate,'Q')) as it will be calculating 3 months but we want to filter it for 4 monthly

Cheers!!

use
last_day(to_date(trunc(sysdate,'Q')))+1 or last_day(to_date(trunc(sysdate,'Q')))

Thank you very much , this solved the purpose .