-
Notifications
You must be signed in to change notification settings - Fork 2
/
Copy pathAreas Validación Nuevo Mes SME
173 lines (166 loc) · 6.1 KB
/
Areas Validación Nuevo Mes SME
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
SELECT DISTINCT
a.country_code
,a.city_name
,a.ba_id
,a.ba_name
,a.bd_area_id
,a.bd_area_name
,a.bd_area_owner
,r.is_launched_area
,org.bdm
,org.bdl
FROM (
SELECT DISTINCT
country_code
,city_name
,district_id AS ba_id
,district_name AS ba_name
,area_id AS bd_area_id
,area_name AS bd_area_name
,area_maintainer AS bd_area_owner
FROM soda_international_dwm.dwm_bizopp_wide_d_whole
WHERE concat_ws('-', year, month, day) = '${end_date}'
AND country_code IN ('MX','CO','CR','PE')
--AND area_id = 'bdArea|5764607523118976976'
) AS a
JOIN (
SELECT
country_code,
city_name,
area_id,
area_type,
CASE WHEN is_valid = 3 THEN 1 ELSE 0 END AS is_launched_area,
is_valid,
is_del
FROM
soda_international_dwd.dwd_zone_info_d_whole
WHERE
concat_ws('-', year, month, day) = '${end_date}'
AND country_code IN ('MX', 'CO', 'CR', 'PE')
AND is_del = 0
AND (
(CASE WHEN is_valid = 3 THEN 1 ELSE 0 END = 1)
OR
-- BD Areas no lanzadas pero deben salir
CONCAT('bdArea|', area_id) IN ('bdArea|216318345824')
)
AND CONCAT('bdArea|', area_id) NOT IN (
'bdArea|4472439022','bdArea|195904667749','bdArea|182302539974','bdArea|240515285246',
'bdArea|5764607671685418186','bdArea|238128726253','bdArea|118695919712',
'bdArea|53659041957','bdArea|203357946046','bdArea|265081323607','bdArea|85246345407',
'bdArea|5764607738949470597','bdArea|5764607628098210175','bdArea|5764607576566991240',
'bdArea|5764607524704422284','bdArea|5764607627817191807','bdArea|5764607661195463070',
'bdArea|5764607549736026351','bdArea|5764607616945553649','bdArea|5764607551875122958',
'bdArea|5764607570485249797','bdArea|5764607657521252106','bdArea|5764607779336423171',
'bdArea|5764607662726383371','bdArea|5764607780166895363','bdArea|5764607780456302339',
'bdArea|5764607562314745716','bdArea|174480162870','bdArea|178334728237','bdArea|83203719237',
'bdArea|109489422514','bdArea|191211241556','bdArea|5764607774051600136',
'bdArea|5764607738139969291','bdArea|53755511014','bdArea|91235811401','bdArea|86416556508',
'bdArea|115600523501','bdArea|174736015447','bdArea|240263627006','bdArea|27746631910',
'bdArea|5764607578525729523','bdArea|5764607571101811258','bdArea|5764607732343440178',
'bdArea|5764607544874828595','bdArea|5764607732356023090','bdArea|5764607732578321202'
)
) AS r
ON a.bd_area_id = CONCAT('bdArea|',r.area_id)
LEFT JOIN (
SELECT DISTINCT
a.country_code
,a.bd
--,a.bdm
--,b.bdl
,IF(
a.bdm IN ('sergiocanal','juanpablonostitajer') OR b.bdl = 'tomasjaramillo'
,a.bd
,a.bdm
) AS bdm
,IF(
a.bdm IN ('sergiocanal','juanpablonostitajer') OR b.bdl = 'tomasjaramillo'
,a.bdm
,b.bdl
) AS bdl
FROM (
SELECT
country_code
,id
,user_id
,username AS bd
,full_name
,CASE WHEN job_role = 1 THEN 'Hunter'
WHEN job_role = 2 THEN 'Farmer'
WHEN job_role = 3 THEN 'ELC'
WHEN job_role = 4 THEN 'hybrid'
WHEN job_role = 5 THEN 'recovery'
ELSE job_role
END AS job_role
,CASE WHEN position = 100 THEN 'BD Jr Specialist'
WHEN position = 101 THEN 'BD Specialist'
WHEN position = 102 THEN 'BD Sr. Specialist'
WHEN position = 103 THEN 'BD Coordinator'
WHEN position = 104 THEN 'KA Specialist KA'
WHEN position = 105 THEN 'CKA Specialist CKA'
WHEN position = 106 THEN 'BDM'
WHEN position = 107 THEN 'CKA BDM'
WHEN position = 108 THEN 'KA BDM'
WHEN position = 109 THEN 'BD Lead'
WHEN position = 110 THEN 'CKA Coordinator'
WHEN position = 111 THEN 'Tele-sales Coodinator'
WHEN position = 200 THEN 'Phone Sales'
WHEN position = 201 THEN 'Phone Sales Manager'
WHEN position = 300 THEN 'BD Support'
WHEN position = 301 THEN 'BD Support Coordinator'
WHEN position = 400 THEN 'CS Supervisor'
WHEN position = 401 THEN 'CS Analyst'
WHEN position = 402 THEN 'CS Coodinator'
ELSE position
END AS position
,CASE WHEN user_status = 1 THEN 'In Draft'
WHEN user_status = 2 THEN 'Entring'
WHEN user_status = 3 THEN 'On-the-job'
WHEN user_status = 4 THEN 'Onboarding Failure'
WHEN user_status = 5 THEN 'On-the-job but have applied for separation'
WHEN user_status = 6 THEN 'Leave'
WHEN user_status = 7 THEN 'On-the-job but transfer in progress'
ELSE user_status
END AS user_status
--,city.city_name service_city_name
,IF(
coordinator = ''
,superior
,coordinator
) AS bdm
FROM soda_international_dwd.dwd_shop_bd_user_info_d_whole bd
WHERE concat_ws('-', year, month, day) = '${end_date}'
AND country_code IN ('MX','CO','CR','PE')
AND user_status NOT IN (1,4,6)
--AND username = 'jimenez.ei.at_v'
) AS a
LEFT JOIN (
SELECT
country_code
,username AS bdm
,IF(
coordinator = ''
,superior
,coordinator
) AS bdl
FROM soda_international_dwd.dwd_shop_bd_user_info_d_whole
WHERE concat_ws('-', year, month, day) = '${end_date}'
AND country_code IN ('MX','CO','CR','PE')
AND user_status NOT IN (1,4,6)
) AS b
ON a.country_code = b.country_code
AND a.bdm = b.bdm
JOIN (
SELECT DISTINCT
country_code
,area_maintainer AS bd_area_owner
FROM soda_international_dwm.dwm_bizopp_wide_d_whole
WHERE concat_ws('-', year, month, day) = '${end_date}'
AND country_code IN ('MX','CO','CR','PE')
) AS c
ON a.country_code = c.country_code
AND a.bd = c.bd_area_owner
) AS org
ON a.country_code = org.country_code
AND a.bd_area_owner = org.bd
ORDER BY a.country_code,a.city_name,a.ba_name,a.bd_area_name