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
浙公网安备 33010602011771号