WITH TMP_EXECUTOR(EXECUTOR,EXECUTORNAME) AS
( SELECT DISTINCT T.EXECUTOR ,T1.FULLNAME AS EXECUTORNAME
FROM (SELECT DISTINCT T1.TASKID
, T2.EXECUTOR
FROM RT_CALLTASKDEF T1
LEFT JOIN RT_CALLTASKREC T2 ON T1.TASKID = T2.TASKID
WHERE T1.TASKTYPE = '02' AND DATE (T1.EXPECTSTARTDATE) <= '2015-02-09') T
LEFT JOIN CM_STAFF T1 ON T1.STAFFPKID = T.EXECUTOR
WHERE T.EXECUTOR IS NOT NULL )
SELECT DISTINCT EXECUTOR, EXECUTORNAME,
MAX(TASKCOUNT) OVER(PARTITION BY EXECUTOR) AS TASKCOUNT,
MAX(PRESOLICITCOUNT) OVER(PARTITION BY EXECUTOR) AS PRESOLICITCOUNT,
MAX(SOLICITINGCOUNT) OVER(PARTITION BY EXECUTOR) AS SOLICITINGCOUNT,
MAX(ACTIVEBOOKCOUNT) OVER(PARTITION BY EXECUTOR) AS ACTIVEBOOKCOUNT,
MAX(ACTIVEBOOKTOSHOPCOUNT) OVER(PARTITION BY EXECUTOR) AS ACTIVEBOOKTOSHOPCOUNT,
MAX(ACTIVEBOOKINSHOPCOUNT) OVER(PARTITION BY EXECUTOR) AS ACTIVEBOOKINSHOPCOUNT,
MAX(PASSIVEBOOKCOUNT) OVER(PARTITION BY EXECUTOR) AS PASSIVEBOOKCOUNT,
MAX(BACKEDFACTORYCOUNT) OVER(PARTITION BY EXECUTOR) AS BACKEDFACTORYCOUNT,
MAX(DEFEATCOUNT) OVER(PARTITION BY EXECUTOR) AS DEFEATCOUNT,
MAX(CALLSUCCESSCOUNT) OVER(PARTITION BY EXECUTOR) AS CALLSUCCESSCOUNT,
MAX(CALLTIMELENGTH) OVER(PARTITION BY EXECUTOR) AS CALLTIMELENGTH
FROM (
SELECT T1.EXECUTOR,T1.EXECUTORNAME,COUNT(T2.TASKID) AS TASKCOUNT, 0 AS PRESOLICITCOUNT, 0 AS SOLICITINGCOUNT
, 0 AS ACTIVEBOOKCOUNT, 0 AS ACTIVEBOOKTOSHOPCOUNT, 0 AS ACTIVEBOOKINSHOPCOUNT, 0 AS PASSIVEBOOKCOUNT, 0 AS BACKEDFACTORYCOUNT
, 0 AS DEFEATCOUNT, 0 AS CALLSUCCESSCOUNT, 0 AS CALLTIMELENGTH
FROM TMP_EXECUTOR T1
LEFT JOIN RT_CALLTASKREC T2 ON T1.EXECUTOR = T2.EXECUTOR WHERE (T2.TASKSTATUS = '20' OR T2.TASKSTATUS = '30') GROUP BY T1.EXECUTOR, T1.EXECUTORNAME
UNION ALL
SELECT T1.EXECUTOR,T1.EXECUTORNAME,0 AS TASKCOUNT, COUNT(T2.TASKID) AS PRESOLICITCOUNT, 0 AS SOLICITINGCOUNT
, 0 AS ACTIVEBOOKCOUNT, 0 AS ACTIVEBOOKTOSHOPCOUNT, 0 AS ACTIVEBOOKINSHOPCOUNT, 0 AS PASSIVEBOOKCOUNT, 0 AS BACKEDFACTORYCOUNT
, 0 AS DEFEATCOUNT, 0 AS CALLSUCCESSCOUNT, 0 AS CALLTIMELENGTH
FROM TMP_EXECUTOR T1
LEFT JOIN RT_CALLTASKREC T2 ON T1.EXECUTOR = T2.EXECUTOR WHERE (T2.TASKSTATUS = '20') GROUP BY T1.EXECUTOR, T1.EXECUTORNAME
UNION ALL
SELECT T1.EXECUTOR,T1.EXECUTORNAME,0 AS TASKCOUNT, 0 AS PRESOLICITCOUNT, COUNT(T2.TASKID) AS SOLICITINGCOUNT
, 0 AS ACTIVEBOOKCOUNT, 0 AS ACTIVEBOOKTOSHOPCOUNT, 0 AS ACTIVEBOOKINSHOPCOUNT, 0 AS PASSIVEBOOKCOUNT, 0 AS BACKEDFACTORYCOUNT
, 0 AS DEFEATCOUNT, 0 AS CALLSUCCESSCOUNT, 0 AS CALLTIMELENGTH
FROM TMP_EXECUTOR T1
LEFT JOIN RT_CALLTASKREC T2 ON T1.EXECUTOR = T2.EXECUTOR WHERE (T2.TASKSTATUS = '30') GROUP BY T1.EXECUTOR, T1.EXECUTORNAME
UNION ALL
SELECT T1.EXECUTOR,T1.EXECUTORNAME,0 AS TASKCOUNT, 0 AS PRESOLICITCOUNT, 0 AS SOLICITINGCOUNT
, COUNT(T2.BOOKINGNO) AS ACTIVEBOOKCOUNT, 0 AS ACTIVEBOOKTOSHOPCOUNT, 0 AS ACTIVEBOOKINSHOPCOUNT, 0 AS PASSIVEBOOKCOUNT, 0 AS BACKEDFACTORYCOUNT
, 0 AS DEFEATCOUNT, 0 AS CALLSUCCESSCOUNT, 0 AS CALLTIMELENGTH
FROM TMP_EXECUTOR T1
LEFT JOIN
(
SELECT DISTINCT T1.EXECUTOR,T2.BOOKINGNO FROM RT_CALLTASKREC T1
LEFT JOIN RT_CALLTASKRECDT T2 ON T1.TASKID = T2.TASKID
LEFT JOIN RT_BOOKING T3 ON T2.BOOKINGNO = T3.BOOKINGNO WHERE T2.BOOKINGNO IS NOT NULL
) T2 ON T1.EXECUTOR = T2.EXECUTOR GROUP BY T1.EXECUTOR, T1.EXECUTORNAME
UNION ALL
SELECT T1.EXECUTOR,T1.EXECUTORNAME,0 AS TASKCOUNT, 0 AS PRESOLICITCOUNT, 0 AS SOLICITINGCOUNT
, 0 AS ACTIVEBOOKCOUNT, COUNT(T2.BOOKINGNO) AS ACTIVEBOOKTOSHOPCOUNT, 0 AS ACTIVEBOOKINSHOPCOUNT, 0 AS PASSIVEBOOKCOUNT, 0 AS BACKEDFACTORYCOUNT
, 0 AS DEFEATCOUNT, 0 AS CALLSUCCESSCOUNT, 0 AS CALLTIMELENGTH
FROM TMP_EXECUTOR T1
LEFT JOIN
(
SELECT DISTINCT T1.EXECUTOR,T2.BOOKINGNO FROM RT_CALLTASKREC T1
LEFT JOIN RT_CALLTASKRECDT T2 ON T1.TASKID = T2.TASKID
LEFT JOIN RT_BOOKING T3 ON T2.BOOKINGNO = T3.BOOKINGNO WHERE T2.BOOKINGNO IS NOT NULL
) T2 ON T1.EXECUTOR = T2.EXECUTOR GROUP BY T1.EXECUTOR, T1.EXECUTORNAME
UNION ALL
SELECT T1.EXECUTOR,T1.EXECUTORNAME,0 AS TASKCOUNT, 0 AS PRESOLICITCOUNT, 0 AS SOLICITINGCOUNT
, 0 AS ACTIVEBOOKCOUNT, 0 AS ACTIVEBOOKTOSHOPCOUNT, COUNT(T2.BOOKINGNO) AS ACTIVEBOOKINSHOPCOUNT, 0 AS PASSIVEBOOKCOUNT, 0 AS BACKEDFACTORYCOUNT
, 0 AS DEFEATCOUNT, 0 AS CALLSUCCESSCOUNT, 0 AS CALLTIMELENGTH
FROM TMP_EXECUTOR T1
LEFT JOIN
(
SELECT DISTINCT T1.EXECUTOR,T2.BOOKINGNO FROM RT_CALLTASKREC T1
LEFT JOIN RT_CALLTASKRECDT T2 ON T1.TASKID = T2.TASKID
LEFT JOIN RT_BOOKING T3 ON T2.BOOKINGNO = T3.BOOKINGNO WHERE T2.BOOKINGNO IS NOT NULL
) T2 ON T1.EXECUTOR = T2.EXECUTOR GROUP BY T1.EXECUTOR, T1.EXECUTORNAME
UNION ALL
SELECT T1.EXECUTOR,T1.EXECUTORNAME,0 AS TASKCOUNT, 0 AS PRESOLICITCOUNT, 0 AS SOLICITINGCOUNT
, 0 AS ACTIVEBOOKCOUNT, 0 AS ACTIVEBOOKTOSHOPCOUNT, 0 AS ACTIVEBOOKINSHOPCOUNT, COUNT(T2.BOOKINGNO) AS PASSIVEBOOKCOUNT, 0 AS BACKEDFACTORYCOUNT
, 0 AS DEFEATCOUNT, 0 AS CALLSUCCESSCOUNT, 0 AS CALLTIMELENGTH
FROM TMP_EXECUTOR T1
LEFT JOIN
(
SELECT DISTINCT T1.EXECUTOR,T2.BOOKINGNO FROM TMP_EXECUTOR T1
LEFT JOIN RT_BOOKING T2 ON T1.EXECUTOR = T2.OPERTATOR WHERE T2.BOOKINGNO IS NOT NULL
) T2 ON T1.EXECUTOR = T2.EXECUTOR GROUP BY T1.EXECUTOR, T1.EXECUTORNAME
UNION ALL
SELECT T1.EXECUTOR,T1.EXECUTORNAME,0 AS TASKCOUNT, 0 AS PRESOLICITCOUNT, 0 AS SOLICITINGCOUNT
, 0 AS ACTIVEBOOKCOUNT, 0 AS ACTIVEBOOKTOSHOPCOUNT, 0 AS ACTIVEBOOKINSHOPCOUNT, 0 AS PASSIVEBOOKCOUNT, COUNT(T2.TASKID) AS BACKEDFACTORYCOUNT
, 0 AS DEFEATCOUNT, 0 AS CALLSUCCESSCOUNT, 0 AS CALLTIMELENGTH
FROM TMP_EXECUTOR T1
LEFT JOIN RT_CALLTASKREC T2 ON T1.EXECUTOR = T2.EXECUTOR WHERE (T2.TASKSTATUS = '40') GROUP BY T1.EXECUTOR, T1.EXECUTORNAME
UNION ALL
SELECT T1.EXECUTOR,T1.EXECUTORNAME,0 AS TASKCOUNT, 0 AS PRESOLICITCOUNT, 0 AS SOLICITINGCOUNT
, 0 AS ACTIVEBOOKCOUNT, 0 AS ACTIVEBOOKTOSHOPCOUNT, 0 AS ACTIVEBOOKINSHOPCOUNT, 0 AS PASSIVEBOOKCOUNT, 0 AS BACKEDFACTORYCOUNT
, COUNT(T2.TASKID) AS DEFEATCOUNT, 0 AS CALLSUCCESSCOUNT, 0 AS CALLTIMELENGTH
FROM TMP_EXECUTOR T1
LEFT JOIN RT_CALLTASKREC T2 ON T1.EXECUTOR = T2.EXECUTOR WHERE (T2.TASKSTATUS = '49') GROUP BY T1.EXECUTOR, T1.EXECUTORNAME
UNION ALL
SELECT T1.EXECUTOR,T1.EXECUTORNAME,0 AS TASKCOUNT, 0 AS PRESOLICITCOUNT, 0 AS SOLICITINGCOUNT
, 0 AS ACTIVEBOOKCOUNT, 0 AS ACTIVEBOOKTOSHOPCOUNT, 0 AS ACTIVEBOOKINSHOPCOUNT, 0 AS PASSIVEBOOKCOUNT, 0 AS BACKEDFACTORYCOUNT
, 0 AS DEFEATCOUNT, COUNT(T2.TALKNOTESID) AS CALLSUCCESSCOUNT, 0 AS CALLTIMELENGTH
FROM TMP_EXECUTOR T1
LEFT JOIN
(
SELECT DISTINCT T1.EXECUTOR,T4.TALKNOTESID FROM RT_CALLTASKREC T1
LEFT JOIN RT_CALLTASKRECDT T2 ON T1.TASKID = T2.TASKID
LEFT JOIN RT_CALLTASKVOICE T3 ON T2.TASKID = T3.TASKID AND T2.LINENO = T3.LINENO
LEFT JOIN RS_TALKNOTES T4 ON T3.VOICEID = T4.TALKNOTESID
WHERE VALUE(T4.ISCALLIN,'Y') <> 'Y' AND T4.TALKLENGTH > 30000
) T2 ON T1.EXECUTOR = T2.EXECUTOR GROUP BY T1.EXECUTOR, T1.EXECUTORNAME
UNION ALL
SELECT T1.EXECUTOR,T1.EXECUTORNAME,0 AS TASKCOUNT, 0 AS PRESOLICITCOUNT, 0 AS SOLICITINGCOUNT
, 0 AS ACTIVEBOOKCOUNT, 0 AS ACTIVEBOOKTOSHOPCOUNT, 0 AS ACTIVEBOOKINSHOPCOUNT, 0 AS PASSIVEBOOKCOUNT, 0 AS BACKEDFACTORYCOUNT
, 0 AS DEFEATCOUNT, 0 AS CALLSUCCESSCOUNT, VALUE(T2.CALLTIMELENGTH ,0) AS CALLTIMELENGTH
FROM TMP_EXECUTOR T1
LEFT JOIN
(
SELECT DISTINCT T1.EXECUTOR,SUM(T4.TALKLENGTH) AS CALLTIMELENGTH FROM RT_CALLTASKREC T1
LEFT JOIN RT_CALLTASKRECDT T2 ON T1.TASKID = T2.TASKID
LEFT JOIN RT_CALLTASKVOICE T3 ON T2.TASKID = T3.TASKID AND T2.LINENO = T3.LINENO
LEFT JOIN RS_TALKNOTES T4 ON T3.VOICEID = T4.TALKNOTESID
WHERE VALUE(T4.ISCALLIN,'Y') <> 'Y' AND T4.TALKLENGTH > 30000 GROUP BY T1.EXECUTOR
) T2 ON T1.EXECUTOR = T2.EXECUTOR
) X
