EXCEL SQL 客户经理制自动分类客户

VBA 客户经理制自动分类客户_20191204203337.png

在设定号的模板版文件中清洗好相关数据，利用SQL语句自动实现客户经理制的自动分类客户，具体SQL语句如下：

SELECT DISTINCT M.车主,M.车系,M.车牌号,M.VIN,M.购车日期,MID(M.购车日期,1,4) AS 购车年份,A.频次,B.续保生效,C.新保生效,D.销售日期,
IIF(车系 IN ("TY","HF","XL","C50"),"L",IIF(车系 IN ("H6","H6C","FJ","JY","LA"),"M","S")) AS LMS,
LMS+IIF(A.频次>=1 AND B.续保生效 IS NOT NULL ,"A",IIF(A.频次 IS NULL AND B.续保生效 IS NOT NULL ,"C",IIF(A.频次 >=1 AND B.续保生效 IS NULL AND C.新保生效 IS NULL ,"B",IIF(D.销售日期 IS NOT NULL ,"D","X"))))+
IIF(A.频次>=2 ,"2",IIF(A.频次 =1,"1","0")) AS 客户分类
FROM ((([客户基盘$A1:E] M
LEFT JOIN (SELECT VIN,COUNT(开单日期) AS 频次 FROM (SELECT VIN,开单日期 FROM [DMS$A1:C] GROUP BY VIN,开单日期) GROUP BY VIN) A ON M.VIN=A.VIN)
LEFT JOIN (SELECT VIN,起保日期 AS 续保生效 FROM [续保台账$A1:C] ) B ON M.VIN=B.VIN)
LEFT JOIN (SELECT VIN,起保日期 AS 新保生效 FROM [新保台账$A1:B] ) C ON M.VIN=C.VIN)
LEFT JOIN (SELECT VIN,销售日期 FROM [新车台账$A1:C] ) D ON M.VIN=D.VIN
WHERE M.VIN IS NOT NULL
ORDER BY M.车牌号