· 8 years ago · Dec 25, 2017, 12:58 AM
1IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[DM_Staging].[dbo].[WORK_PersonEmail]') AND type in (N'U'))
2 DROP TABLE DM_Staging.dbo.WORK_PersonEmail
3GO
4
5CREATE TABLE DM_Staging.dbo.WORK_PersonEmail(
6 PersonEmailId int identity(1, 1) primary key clustered,
7 PersonId int,
8 EmailCategoryId int,
9 qnt_email_address varchar(100),
10 PreferredIndicator bit,
11 NoContactIndicator bit,
12 qnt_phone_key int,
13 rec_sts char(1),
14 ent_oper int,
15 ent_date smalldatetime,
16 chg_oper int,
17 chg_date smalldatetime
18)
19
20go
21
22insert into DM_Staging.dbo.WORK_PersonEmail
23select * from (
24select a.person_id PersonId,
25 c.EmailCategoryId,
26 SUBSTRING(LTRIM(RTRIM(REPLACE(a.email_3, 'Donotuse ', ''))), 1, 100) qnt_email_address,
27 CASE WHEN pref_email_addr_type_id = 3 THEN CAST(1 as bit)
28 ELSE CAST(0 as bit) END PreferredIndicator,
29 CASE WHEN a.email_3 LIKE 'Donotuse%' THEN CAST(1 as bit)
30 ELSE CAST(0 as bit) END NoContactIndicator,
31 NULL qnt_phone_key,
32 a.rec_sts , a.ent_oper, a.ent_date, a.chg_oper, a.chg_date
33--select COUNT(1) --748,928 --748,915 --748,915
34from questnt.dbo.peo_aux_info a with (nolock)
35join questnt.dbo.peo_master b with (nolock)
36 on a.person_id = b.person_id
37join q2.dbo.EmailCategory c with (nolock)
38 on c.Code = 'PE'
39WHERE LTRIM(RTRIM(a.email_3)) <> ''
40and a.email_3 like '%@%'
41
42union
43
44select a.person_id PersonId,
45 c.EmailCategoryId,
46 SUBSTRING(LTRIM(RTRIM(REPLACE(a.member_email, 'Donotuse ', ''))), 1, 100) qnt_email_address,
47 CASE WHEN pref_email_addr_type_id = 4 THEN CAST(1 as bit)
48 ELSE CAST(0 as bit) END PreferredIndicator,
49 CASE WHEN a.member_email LIKE 'Donotuse%' THEN CAST(1 as bit)
50 ELSE CAST(0 as bit) END NoContactIndicator,
51 NULL qnt_phone_key,
52 a.rec_sts , a.ent_oper, a.ent_date, a.chg_oper, a.chg_date
53--select COUNT(1) --275,860 --275,859 --275,859
54from questnt.dbo.peo_aux_info a with (nolock)
55join questnt.dbo.peo_master b with (nolock)
56 on a.person_id = b.person_id
57join q2.dbo.EmailCategory c with (nolock)
58 on c.Code = 'WE'
59WHERE LTRIM(RTRIM(a.member_email)) <> ''
60and a.member_email like '%@%'
61
62union
63
64select a.person_id PersonId,
65 d.EmailCategoryId,
66 SUBSTRING(LTRIM(RTRIM(b.phone)),1,100) qnt_email_address,
67 CAST(0 as bit) PreferredIndicator,
68 CAST(0 as bit) NoContactIndicator,
69 b.phone_key qnt_phone_key,
70 b.rec_sts, b.ent_oper, b.ent_date, b.chg_oper, b.chg_date
71--select COUNT(1) --925 --925 --925
72from questnt.dbo.peo_aux_info a with (nolock)
73join questnt.dbo.peo_addl_phone b with (nolock)
74 on a.person_id = b.person_id
75join questnt.dbo.peo_master c with (nolock)
76 on a.person_id = c.person_id
77join q2.dbo.EmailCategory d with (nolock)
78 on d.Code = 'OE'
79WHERE LTRIM(RTRIM(b.phone)) <> ''
80and b.phone like '%@%'
81
82union
83
84select a.person_id PersonId,
85 d.EmailCategoryId,
86 SUBSTRING(LTRIM(RTRIM(b.description)),1,100) qnt_email_address,
87 CAST(0 as bit) PreferredIndicator,
88 CAST(0 as bit) NoContactIndicator,
89 b.phone_key qnt_phone_key,
90 b.rec_sts, b.ent_oper, b.ent_date, b.chg_oper, b.chg_date
91--select COUNT(1) --2,448 --2,448 --2,448
92from questnt.dbo.peo_aux_info a with (nolock)
93join questnt.dbo.peo_addl_phone b with (nolock)
94 on a.person_id = b.person_id
95join questnt.dbo.peo_master c with (nolock)
96 on a.person_id = c.person_id
97join q2.dbo.EmailCategory d with (nolock)
98 on d.Code = 'OE'
99WHERE LTRIM(RTRIM(b.description)) <> ''
100and b.description like '%@%'
101and b.phone not like '%@%'
102) un