-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathWarmUp_Step2.sql
72 lines (64 loc) · 3.25 KB
/
WarmUp_Step2.sql
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
/*
STEP 2
[BR]
NOTA:
- Query para separar os domínios em Data Extensions
[US]
NOTE:
- Query to separate domains into Data Extensions
*/
--------------> GMAIL
SELECT
[COLUMN_SUBSCRIBERKEY],
[COLUMN_EMAIL]
FROM
[Your_Data_Extension]
WHERE
(
SUBSTRING([COLUMN_EMAIL], CHARINDEX('@',[COLUMN_EMAIL]) + 1, LEN([COLUMN_EMAIL])) LIKE '%gmail.com%'
OR SUBSTRING([COLUMN_EMAIL], CHARINDEX('@',[COLUMN_EMAIL]) + 1, LEN([COLUMN_EMAIL])) LIKE '%googlemail%') --> Data extension Destination [Your_Data_Extension_GMAIL] - Overwrite
--------------> HOTMAIL
SELECT
[COLUMN_SUBSCRIBERKEY],
[COLUMN_EMAIL]
FROM
[Your_Data_Extension]
WHERE
(
SUBSTRING([COLUMN_EMAIL], CHARINDEX('@',[COLUMN_EMAIL]) + 1, LEN([COLUMN_EMAIL])) LIKE '%hotmail.com%'
OR SUBSTRING([COLUMN_EMAIL], CHARINDEX('@',[COLUMN_EMAIL]) + 1, LEN([COLUMN_EMAIL])) LIKE '%outlook.com%'
OR SUBSTRING([COLUMN_EMAIL], CHARINDEX('@',[COLUMN_EMAIL]) + 1, LEN([COLUMN_EMAIL])) LIKE '%live%'
OR SUBSTRING([COLUMN_EMAIL], CHARINDEX('@',[COLUMN_EMAIL]) + 1, LEN([COLUMN_EMAIL])) LIKE '%msn%'
OR SUBSTRING([COLUMN_EMAIL], CHARINDEX('@',[COLUMN_EMAIL]) + 1, LEN([COLUMN_EMAIL])) LIKE '%windowslive%') --> Data extension Destination [Your_Data_Extension_HOTMAIL] - Overwrite
--------------> YAHOO
SELECT
[COLUMN_SUBSCRIBERKEY],
[COLUMN_EMAIL]
FROM
[Your_Data_Extension]
WHERE
(
SUBSTRING([COLUMN_EMAIL], CHARINDEX('@',[COLUMN_EMAIL]) + 1, LEN([COLUMN_EMAIL])) LIKE '%yahoo.com%'
OR SUBSTRING([COLUMN_EMAIL], CHARINDEX('@',[COLUMN_EMAIL]) + 1, LEN([COLUMN_EMAIL])) LIKE '%yahoo.com.br%'
OR SUBSTRING([COLUMN_EMAIL], CHARINDEX('@',[COLUMN_EMAIL]) + 1, LEN([COLUMN_EMAIL])) LIKE '%ymail%'
OR SUBSTRING([COLUMN_EMAIL], CHARINDEX('@',[COLUMN_EMAIL]) + 1, LEN([COLUMN_EMAIL])) LIKE '%verizon%'
OR SUBSTRING([COLUMN_EMAIL], CHARINDEX('@',[COLUMN_EMAIL]) + 1, LEN([COLUMN_EMAIL])) LIKE '%netscape%'
OR SUBSTRING([COLUMN_EMAIL], CHARINDEX('@',[COLUMN_EMAIL]) + 1, LEN([COLUMN_EMAIL])) LIKE '%rogers%'
OR SUBSTRING([COLUMN_EMAIL], CHARINDEX('@',[COLUMN_EMAIL]) + 1, LEN([COLUMN_EMAIL])) LIKE '%sky%'
OR SUBSTRING([COLUMN_EMAIL], CHARINDEX('@',[COLUMN_EMAIL]) + 1, LEN([COLUMN_EMAIL])) LIKE '%aol%'
OR SUBSTRING([COLUMN_EMAIL], CHARINDEX('@',[COLUMN_EMAIL]) + 1, LEN([COLUMN_EMAIL])) LIKE '%rocketmail%'
OR SUBSTRING([COLUMN_EMAIL], CHARINDEX('@',[COLUMN_EMAIL]) + 1, LEN([COLUMN_EMAIL])) LIKE '%btinternet%'
OR SUBSTRING([COLUMN_EMAIL], CHARINDEX('@',[COLUMN_EMAIL]) + 1, LEN([COLUMN_EMAIL])) LIKE '%talk21%') --> Data extension Destination [Your_Data_Extension_YAHOO] - Overwrite
--------------> OTHERS
SELECT
DE.[COLUMN_SUBSCRIBERKEY],
DE.[COLUMN_EMAIL]
FROM
[Your_Data_Extension] DE
WHERE
NOT EXISTS
(SELECT '' FROM [Your_Data_Extension_GMAIL] G WHERE G.[COLUMN_PRIMARYKEY] = DE.[COLUMN_PRIMARYKEY])
AND NOT EXISTS
(SELECT '' FROM [Your_Data_Extension_HOTMAIL] H WHERE H.[COLUMN_PRIMARYKEY] = DE.[COLUMN_PRIMARYKEY])
AND NOT EXISTS
(SELECT '' FROM [Your_Data_Extension_YAHOO] Y WHERE Y.[COLUMN_PRIMARYKEY] = DE.[COLUMN_PRIMARYKEY]) --> Data extension Destination [Your_Data_Extension_OTHERS] - Overwrite