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
With语句在数据统计应用
转载本文章为转载内容,我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题,欢迎原作者联系我们进行内容更正或删除文章。
提问和评论都可以,用心的回复会被更多人看到
评论
发布评论
相关文章
-
文件操作-数据统计
数据统计
#define #include #ifdef 其他