- Created by Binila Russel , last modified on Jun 06, 2024
You are viewing an old version of this content. View the current version.
Compare with Current View Version History
« Previous Version 2 Next »
Snowflake Script used to identify phone numbers shared across multiple contacts
with ac_primary as
(
select ac.id "Alt Channel SF ID:Primary",
ac.name "Alt Channel Name:Primary",
ac.aqb__contact__c "ContactID:Primary",
c.name "Contact Name: Primary",
c.lastname "Lastname:Primary",
ac.aqb__account__c "AccountID:Primary",
regexp_replace(split_part(ac.aqb__linkedto__c , '"',2), '/','') "Linked To:Primary",
ac.aqb__status__c "Status:Primary",
ac.aqb__type__c "Type:Primary",
ac.aqb__phone__c "Phone:Primary",
case
when startswith("Formatted Phone", '0011') then regexp_replace("Formatted Phone", '^(0011)', '')
when startswith("Formatted Phone", '000') then regexp_replace("Formatted Phone", '^(000)', '0')
when startswith("Formatted Phone", '00') then regexp_replace("Formatted Phone", '^(00)', '0')
when startswith("Formatted Phone", '612') then regexp_replace("Formatted Phone", '^(612)', '02')
when startswith("Formatted Phone", '613') then regexp_replace("Formatted Phone", '^(613)', '03')
when startswith("Formatted Phone", '614') then regexp_replace("Formatted Phone", '^(614)', '04')
when startswith("Formatted Phone", '617') then regexp_replace("Formatted Phone", '^(617)', '07')
when startswith("Formatted Phone", '618') then regexp_replace("Formatted Phone", '^(618)', '08')
when startswith("Formatted Phone", '6102') then regexp_replace("Formatted Phone", '^(6102)', '02')
when startswith("Formatted Phone", '6103') then regexp_replace("Formatted Phone", '^(6103)', '03')
when startswith("Formatted Phone", '6104') then regexp_replace("Formatted Phone", '^(6104)', '04')
when startswith("Formatted Phone", '6107') then regexp_replace("Formatted Phone", '^(6107)', '07')
when startswith("Formatted Phone", '6108') then regexp_replace("Formatted Phone", '^(6108)', '08')
else "Formatted Phone"
end "Formatted Phone:Primary",
ac.createddate "CreatedDate:Primary",
ac.lastmodifieddate "LastModifiedDate:Primary",
u.name "CreatedBy:Primary",
u1.name "LastModifiedBy:Primary",
ac.aqb__source__c "Source:Primary",
row_number() over(partition by "Linked To:Primary", "Formatted Phone:Primary" order by "CreatedDate:Primary" desc ) seq
from(select a.*,
regexp_replace(lower(trim(a.aqb__phone__c)), '[^a-z0-9]', '') "Formatted Phone"
from salesforce_db.salesforce_sch.aqb__aqaddress__c a
)ac
left join salesforce_db.salesforce_sch.recordtype r on r.id = ac.recordtypeid
left join salesforce_db.salesforce_sch.user u on u.id = ac.createdbyid
left join salesforce_db.salesforce_sch.user u1 on u1.id = ac.lastmodifiedbyid
left join salesforce_db.salesforce_sch.contact c on left(c.id,15) = "Linked To:Primary"
--left join salesforce_db.salesforce_sch.account a on a.id = ac.aqb__account__c
where
r.name = 'Phone' and "Linked To:Primary" like '003%'
order by 3,7
),
ac_dupes as
(
select ac.id "Alt Channel SF ID:Dupe",
ac.name "Alt Channel Name:Dupe",
ac.aqb__contact__c "ContactID:Dupe",
c.name "Contact Name: Dupe",
c.lastname "Lastname:Dupe",
regexp_replace(split_part(ac.aqb__linkedto__c , '"',2), '/','') "Linked To:Dupe",
ac.aqb__status__c "Status:Dupe",
ac.aqb__type__c "Type:Dupe",
ac.aqb__phone__c "Phone:Dupe",
case
when startswith("Formatted Phone", '0011') then regexp_replace("Formatted Phone", '^(0011)', '')
when startswith("Formatted Phone", '000') then regexp_replace("Formatted Phone", '^(000)', '0')
when startswith("Formatted Phone", '00') then regexp_replace("Formatted Phone", '^(00)', '0')
when startswith("Formatted Phone", '612') then regexp_replace("Formatted Phone", '^(612)', '02')
when startswith("Formatted Phone", '613') then regexp_replace("Formatted Phone", '^(613)', '03')
when startswith("Formatted Phone", '614') then regexp_replace("Formatted Phone", '^(614)', '04')
when startswith("Formatted Phone", '617') then regexp_replace("Formatted Phone", '^(617)', '07')
when startswith("Formatted Phone", '618') then regexp_replace("Formatted Phone", '^(618)', '08')
when startswith("Formatted Phone", '6102') then regexp_replace("Formatted Phone", '^(6102)', '02')
when startswith("Formatted Phone", '6103') then regexp_replace("Formatted Phone", '^(6103)', '03')
when startswith("Formatted Phone", '6104') then regexp_replace("Formatted Phone", '^(6104)', '04')
when startswith("Formatted Phone", '6107') then regexp_replace("Formatted Phone", '^(6107)', '07')
when startswith("Formatted Phone", '6108') then regexp_replace("Formatted Phone", '^(6108)', '08')
else "Formatted Phone"
end "Formatted Phone:Dupe",
ac.createddate "CreatedDate:Dupe",
ac.lastmodifieddate "LastModifiedDate:Dupe",
u.name "CreatedBy:Dupe",
u1.name "LastModifiedBy:Dupe",
ac.aqb__source__c "Source:Dupe",
ac.aqb__linkedto__c,
row_number() over(partition by "Linked To:Dupe" order by "CreatedDate:Dupe" desc ) seq
from(select a.*,
regexp_replace(lower(trim(a.aqb__phone__c)), '[^a-z0-9]', '') "Formatted Phone"
from salesforce_db.salesforce_sch.aqb__aqaddress__c a
)ac
left join salesforce_db.salesforce_sch.recordtype r on r.id = ac.recordtypeid
left join salesforce_db.salesforce_sch.user u on u.id = ac.createdbyid
left join salesforce_db.salesforce_sch.user u1 on u1.id = ac.lastmodifiedbyid
left join salesforce_db.salesforce_sch.contact c on left(c.id,15) = "Linked To:Dupe"
--left join salesforce_db.salesforce_sch.account a on left(a.id,15) = "Linked To:Dupe"
where
r.name = 'Phone' and "Linked To:Dupe" like '003%'
),
Dupes as
( select *,
case
when "Lastname:Primary" = "Lastname:Dupe" then
'Yes'
else
'No'
end "Last Name Match" from (
select * from ac_primary acp
where acp.seq = 1
)dd
join ac_dupes acd on "Formatted Phone:Primary" = "Formatted Phone:Dupe"
where "Alt Channel Name:Primary" <> "Alt Channel Name:Dupe" and "Linked To:Primary" <> "Linked To:Dupe"
order by 2,3,1
)
select "Alt Channel SF ID:Primary",
"Alt Channel Name:Primary",
"ContactID:Primary",
"Contact Name: Primary",
"AccountID:Primary",
"Linked To:Primary",
"Phone:Primary",
"Formatted Phone:Primary",
"Status:Primary",
"Type:Primary",
"LastModifiedDate:Primary",
"CreatedDate:Primary",
"LastModifiedBy:Primary",
"CreatedBy:Primary",
"Source:Primary",
listagg("Linked To:Dupe", ',') within group (order by "LastModifiedDate:Dupe" desc)"Linked To:Dupes",
listagg("Contact Name: Dupe", ',') within group (order by "LastModifiedDate:Dupe" desc) "Contact Name:Dupes",
listagg("Alt Channel SF ID:Dupe", ',') within group (order by "LastModifiedDate:Dupe" desc)"Alt Channel ID:Dupes",
listagg("Alt Channel Name:Dupe", ',') within group (order by "LastModifiedDate:Dupe" desc) "Alt Channel Names:Dupes",
listagg("Phone:Dupe", ',') within group (order by "LastModifiedDate:Dupe" desc) "Phones:Dupes",
listagg("Formatted Phone:Dupe", ',') within group (order by "LastModifiedDate:Dupe" desc) "Formatted Phones:Dupes",
listagg("Status:Dupe", ',') within group (order by "LastModifiedDate:Dupe" desc) "Status:Dupes",
listagg("Type:Dupe", ',') within group (order by "LastModifiedDate:Dupe" desc) "Type:Dupes",
listagg("CreatedBy:Dupe", ',') within group (order by "LastModifiedDate:Dupe" desc) "CreatedBy:Dupes",
listagg("CreatedDate:Dupe", ',') within group (order by "LastModifiedDate:Dupe" desc) "CreatedDate:Dupes",
listagg("LastModifiedBy:Dupe", ',') within group (order by "LastModifiedDate:Dupe" desc) "LastModifiedBy:Dupe",
listagg("LastModifiedDate:Dupe",',') within group (order by "LastModifiedDate:Dupe" desc) "LastModifiedDate:Dupe",
listagg("Source:Dupe", ',') within group (order by "LastModifiedDate:Dupe" desc) "Source:Dupes",
listagg("Last Name Match", ',') within group (order by "LastModifiedDate:Dupe" desc) "Match",
count("Alt Channel SF ID:Dupe") "No of Dupes"
from(
Select PT1.*, Rank() OVER ( PARTITION BY "Linked To:Dupe"
ORDER BY RNK1,RNK2 ASC
) AS RNK3 /* Rank all contact matches by contact_id2 to remove duplicate matches when filtere by rank3 = 1 */
from
(
select d.*, Rank() OVER (
ORDER BY "Linked To:Primary" ASC
) AS RNK1 /* Rank al contact IDs in ascending order */
, Rank() OVER ( PARTITION BY "Linked To:Primary"
ORDER BY "Linked To:Dupe" ASC
) AS RNK2 /* Rank contacts linked to each dontact_Id1 in ascending order */
from dupes d
)PT1
ORDER BY RNK1 ASC,RNK2 ASC
)WHERE RNK3 = 1
and contains("Last Name Match", 'No')
GROUP BY ALL;
- No labels