SELECT a.SubscriberKey,
       a.reason
FROM (
        SELECT s.SubscriberKey,
            'Undeliverable' as reason
        FROM ENT._Subscribers s
        JOIN Test_Retail_TA_News_2024_INCLUDE c on c.SubscriberKey = s.SubscriberKey
        WHERE 1 = 1
            AND s.status in ('held', 'bounced', 'unsubscribe', 'unsubscribed')
            AND s.SubscriberKey IN (
                SELECT x.SubscriberKey
                FROM [Test_Retail_TA_News_2024_INCLUDE] x
                WHERE 1 = 1
            )
             
    ) a

    
UNION ALL
SELECT a.SubscriberKey,
       a.reason
FROM (
        SELECT b.SubscriberKey,  
              'Dealer Exclusion' as reason
        FROM [Test_Retail_TA_News_2024_INCLUDE] 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.SubscriberKey,
       a.reason
FROM (
        SELECT b.SubscriberKey,
            'Hard bounce DE' as reason
        FROM [Test_Retail_TA_News_2024_INCLUDE] b
        WHERE 1 = 1
            AND b.SubscriberKey IN (
                SELECT x.SubscriberKey
                FROM ent.[Hard Bounces - Exclusion] x
            )
    ) a
UNION ALL
SELECT a.SubscriberKey,
       a.reason
FROM (
        SELECT b.SubscriberKey,
            'Cat Agency Exclusion' as reason
        FROM [Test_Retail_TA_News_2024_INCLUDE] 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.SubscriberKey,
       a.reason
FROM (
        SELECT b.SubscriberKey,
            'Competitor Exclusion' as reason
        FROM [Test_Retail_TA_News_2024_INCLUDE] 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 
    
    UNION ALL
SELECT a.SubscriberKey,
       a.reason
FROM (
        SELECT b.SubscriberKey,
            'AMC Status' as reason
        FROM [Test_Retail_TA_News_2024_INCLUDE] b
        WHERE 1 = 1
         AND   b.AMC_Status__c IN ('Inactive', 'Expired')
    ) a 

       UNION ALL
SELECT a.SubscriberKey,
       a.reason
FROM (
        SELECT b.SubscriberKey,
            'AMC Not Marketable' as reason
        FROM [Test_Retail_TA_News_2024_INCLUDE] b
        WHERE 1 = 1
         AND   b.AMC_Last_Activity_Record_ID__c LIKE 'Not Marketable' 
         AND (b.AMC_Status__c LIKE 'Active' OR b.AMC_Status__c IS NULL)
    ) a