VBA 客戶經(jīng)理制自動(dòng)分類客戶_20191204203337.png

在設(shè)定號(hào)的模板版文件中清洗好相關(guān)數(shù)據(jù)，利用SQL語(yǔ)句自動(dòng)實(shí)現(xiàn)客戶經(jīng)理制的自動(dòng)分類客戶，具體SQL語(yǔ)句如下：

SELECT DISTINCT M.車主,M.車系,M.車牌號(hào),M.VIN,M.購(gòu)車日期,MID(M.購(gòu)車日期,1,4) AS 購(gòu)車年份,A.頻次,B.續(xù)保生效,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.續(xù)保生效 IS NOT NULL ,"A",IIF(A.頻次 IS NULL AND B.續(xù)保生效 IS NOT NULL ,"C",IIF(A.頻次 >=1 AND B.續(xù)保生效 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 續(xù)保生效 FROM [續(xù)保臺(tái)賬$A1:C] ) B ON M.VIN=B.VIN)
LEFT JOIN (SELECT VIN,起保日期 AS 新保生效 FROM [新保臺(tái)賬$A1:B] ) C ON M.VIN=C.VIN)
LEFT JOIN (SELECT VIN,銷售日期 FROM [新車臺(tái)賬$A1:C] ) D ON M.VIN=D.VIN
WHERE M.VIN IS NOT NULL
ORDER BY M.車牌號(hào)