tmk业绩统计更新语句

申请免费试用、咨询电话：400-8352-114

    SELECT DISTINCT uExtW_ID, cExtW_Name, cExtW_JobNumber, 回单数, 有效单数, 无效单数, 回访数, 报名数, 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 回单数,
                                              (CASE WHEN yx.sl IS NULL THEN 0 ELSE yx.sl END) AS 有效单数, (CASE WHEN wx.sl IS NULL THEN 0 ELSE wx.sl END) AS 无效单数, (CASE WHEN hf.sl IS NULL
                                              THEN 0 ELSE hf.sl END) AS 回访数, (CASE WHEN bm.sl IS NULL THEN 0 ELSE bm.sl END) AS 报名数, 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 = '预约成功') OR
                                                                                                        (dbo.TMKRecord.cTMKR_Intention = '已沟通未预约') OR
                                                                                                        (dbo.TMKRecord.cTMKR_Intention = '已沟通现在不感兴趣')) 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 = '永远别联系') 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 <> '无效、重复信息') 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