1 SELECT A.ROW_ID, -- 门店编码 2 A.CUSTOMER_NAME, -- 门店名称 3 B.CUSTOMER_STATUS, -- 客户状态 4 C.VALUE CUSTOMER_TYPE, -- 门店类别 5 NVL(D.MONTH_3_AMT, 0) MONTH_3_AMT, -- 三个月均销售金额 6 NVL(E.VISIT_COUNT, 0) BE_VISIT_COUNT, -- 上月拜访次数 7 TO_CHAR(TO_DATE(F.VISI_SYS_DATE, 'yyyy-mm-dd'), 'yyyy-mm-dd') VISI_SYS_DATE, -- 最后拜访时间 8 NVL(B.VISIT_COUNT, 0) VISIT_COUNT_LIST, -- 当月拜访次数 9 CASE 10 WHEN NVL(B.VISIT_COUNT, 0) = 0 THEN 11 '0秒' 12 ELSE 13 DECODE(FLOOR(NVL(NVL(F.SIS, 0) / NVL(B.VISIT_COUNT, 0), 0) / 3600), 14 0, 15 '', 16 FLOOR(NVL(NVL(F.SIS, 0) / NVL(B.VISIT_COUNT, 0), 0) / 3600) || '小时') || 17 DECODE(FLOOR(MOD(NVL(NVL(F.SIS, 0) / NVL(B.VISIT_COUNT, 0), 0), 18 3600) / 60), 19 0, 20 '', 21 FLOOR(MOD(NVL(NVL(F.SIS, 0) / NVL(B.VISIT_COUNT, 0), 0), 22 3600) / 60) || '分') || 23 ROUND(MOD(NVL(NVL(F.SIS, 0) / NVL(B.VISIT_COUNT, 0), 0), 60), 2) || '秒' 24 END AVG_TIME, -- 拜访时间平均时间 25 F.USERNAME, -- 拜访人员 26 A.SALESMAN, -- 业务员 27 BP.NAME SALES_NAME -- 业务员 28 FROM BASE_CUSTOMER A -- 客户表 29 LEFT JOIN SFA_VISIT_ACC_STATUS B -- 客户状态 30 ON A.ROW_ID = B.CUSTOMER_ID 31 AND A.DID = B.DID 32 AND B.VISIT_MONTH = MONTH 33 LEFT JOIN BASE_DICT C -- 客户类型 34 ON A.CUSTOMER_TYPE = C.ROW_ID 35 AND A.DID = C.DID 36 AND C.CODE = '客户类型' 37 LEFT JOIN (SELECT D.CUSTOMER_ID, -- 客户编码 38 ROUND(SUM(D.VISIT_DN_AMT) / 3, 2) MONTH_3_AMT, -- 此客户三个月内销售的平均值 39 D.DID 40 FROM SFA_VISIT_ACC_STATUS D 41 WHERE VISIT_MONTH >= 42 TO_CHAR(ADD_MONTHS(TO_DATE(MONTH, 'yyyymm'), -2), 43 'yyyymm') 44 GROUP BY CUSTOMER_ID, DID) D 45 ON A.ROW_ID = D.CUSTOMER_ID 46 AND A.DID = D.DID 47 LEFT JOIN SFA_VISIT_ACC_STATUS E 48 ON A.ROW_ID = E.CUSTOMER_ID 49 AND A.DID = E.DID 50 AND E.VISIT_MONTH = 51 TO_CHAR(ADD_MONTHS(TO_DATE(MONTH, 'yyyymm'), -1), 'yyyymm') 52 LEFT JOIN (SELECT * 53 FROM (SELECT ROW_NUMBER() OVER(PARTITION BY STORECODE ORDER BY VISIT_ENDTIME DESC) RN, 54 STORECODE, -- 门店编码 55 VISIT_STARTTIME, -- 拜访开始时间 56 VISIT_ENDTIME, -- 拜访结束时间 57 SI, 58 SUM(SI) OVER(PARTITION BY /*USERNAME,*/ STORECODE ORDER BY VISI_SYS_DATE) SIS, 59 USERNAME, 60 VISI_SYS_DATE 61 FROM (SELECT STORECODE, -- 客户ID 62 VISIT_STARTTIME, -- 开始时间 63 NVL(VISIT_ENDTIME, VISIT_STARTTIME) VISIT_ENDTIME, -- 结束时间 64 (TO_DATE(NVL(DECODE(T.VISIT_ENDTIME, 65 '1900-01-01 00:00:00', 66 '', 67 T.VISIT_ENDTIME), 68 VISIT_STARTTIME), 69 'yyyy-mm-dd hh24:mi:ss') - 70 TO_DATE(T.VISIT_STARTTIME, 71 'yyyy-mm-dd hh24:mi:ss')) * 24 * 60 * 60 SI, 72 USERNAME, -- 拜访人员 73 VISI_SYS_DATE -- 拜访时间 74 FROM SFA_VISIT_LIST T 75 WHERE SUBSTR(T.VISI_SYS_DATE, 1, 6) = MONTH 76 ORDER BY STORECODE) 77 ORDER BY STORECODE) 78 WHERE RN = 1) F 79 80 ON A.ROW_ID = F.STORECODE 81 LEFT JOIN BASE_PERSON BP 82 ON A.SALESMAN = BP.ROW_ID 83 AND A.DID = BP.DID 84 WHERE A.DID = pDid 85 --if pKeyWord不为空 86 --if pKey == "全部" 87 AND (UPPER(A.ROW_ID) LIKE '%pKeyWord%' 88 OR UPPER(A.CUSTOMER_NAME) LIKE '%pKeyWord%' 89 OR UPPER(F.USERNAME) LIKE '%pKeyWord%' 90 OR UPPER(BP.Name) LIKE '%pKeyWord%' 91 OR UPPER(C.VALUE) LIKE '%pKeyWord%' 92 OR UPPER(B.CUSTOMER_STATUS) LIKE '%pKeyWord%' 93 ) 94 --if pKey == "客户" 95 AND (UPPER(A.ROW_ID) LIKE '%pKeyWord%' 96 OR UPPER(A.CUSTOMER_NAME) LIKE '%pKeyWord%' 97 ) 98 --if pKey == "拜访人员" 99 AND (UPPER(F.USERNAME) LIKE '%pKeyWord%' ) 100 --if pKey == "业务员" 101 AND (UPPER(BP.Name) LIKE '%pKeyWord%' ) 102 --if pKey == "客户类型" 103 AND (UPPER(C.VALUE) LIKE '%pKeyWord%') 104 --if pKey == "客户状态" 105 AND (UPPER(B.CUSTOMER_STATUS) LIKE '%pKeyWord%' ) 106 --if W_status不为空 107 AND a.status=W_status 108 ORDER BY VISIT_COUNT_LIST DESC