Newsletter_Exclusions_Dealer_Competitor_Agency_Domains

PHOTO

Wed Jan 31 2024 15:14:41 GMT+0000 (Coordinated Universal Time)

SELECT a.EmailAddress
FROM (
        SELECT b.EmailAddress
        FROM [ep_mr_en_us_w170049_MASTER] b
        WHERE 1 = 1
        AND LOWER(
            RIGHT (
                b.EmailAddress,
                LEN(b.EmailAddress) - CHARINDEX('@', b.EmailAddress)
            )
        ) IN (
            SELECT LOWER(x.Domain)
            FROM ent.[Dealer Domains] x
    )
) a
 
UNION ALL
SELECT a.EmailAddress
FROM (
    SELECT b.EmailAddress
    FROM [ep_mr_en_us_w170049_MASTER] b
    WHERE 1 = 1
    AND LOWER(
        RIGHT (
            b.EmailAddress,
            LEN(b.EmailAddress) - CHARINDEX('@', b.EmailAddress)
            )
        ) IN (
            SELECT LOWER(x.Domain)
            FROM ent.[Cat_Agency_Domains] x
    )
) a
 
UNION ALL
SELECT a.EmailAddress
FROM (
    SELECT b.EmailAddress
    FROM [ep_mr_en_us_w170049_MASTER] b
    WHERE 1 = 1
    AND LOWER(
        RIGHT (
            b.EmailAddress,
            LEN(b.EmailAddress) - CHARINDEX('@', b.EmailAddress)
            )
        ) IN (
            SELECT LOWER(x.Domain)
            FROM ent.[Competitor Domains] x
    )
) a

COPY