tmk業(yè)績統(tǒng)計更新語句

申請免費試用、咨詢電話：400-8352-114

    SELECT DISTINCT uExtW_ID, cExtW_Name, cExtW_JobNumber, 回單數(shù), 有效單數(shù), 無效單數(shù), 回訪數(shù), 報名數(shù), dExtW_CreateTime
FROM         (SELECT     dbo.ExtensionWorkers.uExtW_ID, dbo.ExtensionWorkers.cExtW_Name, dbo.ExtensionWorkers.cExtW_JobNumber, (CASE WHEN hd.sl IS NULL THEN 0 ELSE hd.sl END) AS 回單數(shù),
                                              (CASE WHEN yx.sl IS NULL THEN 0 ELSE yx.sl END) AS 有效單數(shù), (CASE WHEN wx.sl IS NULL THEN 0 ELSE wx.sl END) AS 無效單數(shù), (CASE WHEN hf.sl IS NULL
                                              THEN 0 ELSE hf.sl END) AS 回訪數(shù), (CASE WHEN bm.sl IS NULL THEN 0 ELSE bm.sl END) AS 報名數(shù), dbo.ExtensionWorkers.dExtW_CreateTime
                       FROM          dbo.ExtensionWorkers LEFT OUTER JOIN
                                                  (SELECT     uClu_ExtWId, COUNT(DISTINCT uClu_ID) AS sl
                                                    FROM          dbo.Clue
                                                    WHERE      (1 = 1)
                                                    GROUP BY uClu_ExtWId) AS hd ON hd.uClu_ExtWId = dbo.ExtensionWorkers.uExtW_ID LEFT OUTER JOIN
                                                  (SELECT     Clue_4.uClu_ExtWId, COUNT(DISTINCT T2.uTMKR_CluId) AS sl
                                                    FROM          dbo.Clue AS Clue_4 INNER JOIN
                                                                               (SELECT     dbo.TMKRecord.cTMKR_Intention, T1.uTMKR_CluId
                                                                                 FROM          dbo.TMKRecord INNER JOIN
                                                                                                            (SELECT     uTMKR_CluId, MAX(dTMKR_ContactTime) AS dTMKR_ContactTime
                                                                                                              FROM          dbo.TMKRecord AS TMKRecord_1
                                                                                                              GROUP BY uTMKR_CluId) AS T1 ON T1.uTMKR_CluId = dbo.TMKRecord.uTMKR_CluId AND
                                                                                                        T1.dTMKR_ContactTime = dbo.TMKRecord.dTMKR_ContactTime
                                                                                 WHERE      (dbo.TMKRecord.cTMKR_Intention = '預(yù)約成功') OR
                                                                                                        (dbo.TMKRecord.cTMKR_Intention = '已溝通未預(yù)約') OR
                                                                                                        (dbo.TMKRecord.cTMKR_Intention = '已溝通現(xiàn)在不感興趣')) AS T2 ON T2.uTMKR_CluId = Clue_4.uClu_ID
                                                    WHERE      (1 = 1)
                                                    GROUP BY Clue_4.uClu_ExtWId) AS yx ON yx.uClu_ExtWId = dbo.ExtensionWorkers.uExtW_ID LEFT OUTER JOIN
                                                  (SELECT     Clue_3.uClu_ExtWId, COUNT(DISTINCT T2_1.uTMKR_CluId) AS sl
                                                    FROM          dbo.Clue AS Clue_3 INNER JOIN
                                                                               (SELECT     TMKRecord_2.cTMKR_Intention, T1_1.uTMKR_CluId
                                                                                 FROM          dbo.TMKRecord AS TMKRecord_2 INNER JOIN
                                                                                                            (SELECT     uTMKR_CluId, MAX(dTMKR_ContactTime) AS dTMKR_ContactTime
                                                                                                              FROM          dbo.TMKRecord AS TMKRecord_1
                                                                                                              GROUP BY uTMKR_CluId) AS T1_1 ON T1_1.uTMKR_CluId = TMKRecord_2.uTMKR_CluId AND
                                                                                                        T1_1.dTMKR_ContactTime = TMKRecord_2.dTMKR_ContactTime
                                                                                 WHERE      (TMKRecord_2.cTMKR_Intention = '永遠(yuǎn)別聯(lián)系') OR
                                                                                                        (TMKRecord_2.cTMKR_Intention = '未溝通')) AS T2_1 ON T2_1.uTMKR_CluId = Clue_3.uClu_ID
                                                    WHERE      (1 = 1)
                                                    GROUP BY Clue_3.uClu_ExtWId) AS wx ON wx.uClu_ExtWId = dbo.ExtensionWorkers.uExtW_ID LEFT OUTER JOIN
                                                  (SELECT     uClu_ExtWId, COUNT(DISTINCT uClu_ExtWId) AS sl
                                                    FROM          dbo.Clue AS Clue_2
                                                    WHERE      (cClu_State <> '無效、重復(fù)信息') AND (uClu_ExtWId <> '00000000-0000-0000-0000-000000000000')
                                                    GROUP BY uClu_ExtWId) AS hf ON hd.uClu_ExtWId = dbo.ExtensionWorkers.uExtW_ID LEFT OUTER JOIN
                                                  (SELECT     Clue_1.uClu_ExtWId, COUNT(*) AS sl
                                                    FROM          dbo.Clue AS Clue_1 INNER JOIN
                                                                               (SELECT     uCha_CluId
                                                                                 FROM          dbo.Charge AS Charge_1
                                                                                 GROUP BY uCha_CluId) AS Charge ON Charge.uCha_CluId = Clue_1.uClu_ID
                                                    WHERE      (1 = 1) AND (Clue_1.uClu_ExtWId <> '00000000-0000-0000-0000-000000000000')
                                                    GROUP BY Clue_1.uClu_ExtWId) AS bm ON hd.uClu_ExtWId = dbo.ExtensionWorkers.uExtW_ID) AS a