· 5 years ago · Feb 04, 2020, 05:24 PM
1SELECT
2LEFT(a.cnpj, 10) 'PARENT_COMPANY',
3IFNULL(a.company_name, CONCAT(a.first_name,' ',a.last_name)) 'COMPANY_NAME',
4SUBSTRING(a.email,INSTR(a.email,'@')) 'CUSTOMER_DOMAIN',
5COUNT(1) 'COUNT_ID',
6b.sales_num 'SELLER_NUMBERS',
7ST,
8CASE WHEN SUD LIKE 43 THEN 'RICARDO IN' ELSE 'OK' END AS 'TESTE_PRO',
9CASE WHEN b.sales_num >1 THEN '+1 VENDEDOR' ELSE 'OK' END AS 'ERRORS_MAP',
10COMMERCIAL_V,
11PRINTI_PRO_V,
12CUSTOMER_V
13
14FROM customer a
15
16LEFT JOIN
17 (SELECT
18 LEFT(Z.cnpj, 10) 'PARENT_COMPANY',
19 SUBSTRING(Z.email,INSTR(Z.email,'@')),
20 Z.service_type,
21 CASE WHEN Z.service_type LIKE 'COMMERCIAL' THEN 1 END AS 'COMMERCIAL_V',
22 CASE WHEN Z.service_type LIKE 'PRINTI_PRO' THEN 1 END AS 'PRINTI_PRO_V',
23 CASE WHEN Z.service_type LIKE 'CUSTOMER_SERVICE' THEN 1 END AS 'CUSTOMER_V'
24 FROM customer Z) sub1
25 ON sub1.PARENT_COMPANY = PARENT_COMPANY
26
27LEFT JOIN
28 (SELECT
29 LEFT(cnpj, 10) 'id' ,
30 IFNULL(company_name, CONCAT(first_name,' ',last_name)) 'name_comp',
31 COUNT(DISTINCT(sales_user_id)) 'SALES_NUM',
32 sales_user_id 'SUD',
33 service_type 'ST'
34 FROM customer
35 WHERE LEFT(cnpj, 10) != ''
36 AND sales_user_id IS NOT NULL
37 GROUP BY
38 LEFT(cnpj, 10),
39 IFNULL(company_name, CONCAT(first_name,' ',last_name))) b
40 ON LEFT(a.cnpj, 10) = b.id
41 AND IFNULL(a.company_name, CONCAT(a.first_name,' ',a.last_name)) = b.name_comp
42 WHERE LEFT(a.cnpj, 10) IS NOT NULL
43 AND SUBSTRING(a.email,INSTR(a.email,'@')) NOT IN ('@ICLOUD.COM','@GMAIL.COM','@HOTMAIL.COM','@OUTLOOK.COM','@YAHOO.COM.BR','@UOL.COM.BR','@PRINTI.COM.BR','@TERRA.COM.BR','@BOL.COM.BR','@YAHOO.COM','@LIVE.COM','@OUTLOOK.COM.BR','@GLOBO.COM', 'gmail.com')
44 AND a.sales_user_id IS NOT NULL
45 GROUP BY LEFT(a.cnpj, 10),
46 IFNULL(a.company_name, CONCAT(a.first_name,' ',a.last_name)),
47 SUBSTRING(a.email,INSTR(email,'@')),
48 b.sales_num
49
50HAVING COUNT(1) >1
51
52
53LIMIT 2000
54
55
56#-------------------------------------->