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