Upload to CRMi

SELECT
SubscriberKey, First_Name__c, Last_Name__c, Email__c, Cat_Campaign_Most_Recent__c, Business_Unit__c,
CASE WHEN Contact_for_Questions__c IS NULL THEN 'cat_sis2go@cat.com' END AS Contact_for_Questions__c, Mailing_Country__c, Company_Name__c, CreatedDate, DealerCode, MainDealer 
FROM (
SELECT
DISTINCT r.MAIL AS Email__c, SubscriberKey, r.FirstName AS First_Name__c, r.LastName AS Last_Name__c, r.Cat_Campaign_Most_Recent__c, r.Business_Unit__c,
CASE WHEN r.Contact_for_Questions__c IS NULL THEN 'cat_sis2go@cat.com' END AS Contact_for_Questions__c, r.Country AS Mailing_Country__c, r.Organization AS Company_Name__c, r.[Date] AS CreatedDate, r.DealerCode, r.MainDealer,

ROW_NUMBER() OVER(PARTITION BY r.MAIL ORDER BY r.[Date] DESC) as RowNum

FROM [7011G000000OJtMQAW_CI_MR_SIS2GO_Welcome_rawdata] r
LEFT JOIN [7011G000000OJtMQAW_CI_MR_SIS2GO_SIS2GO_Exclude_Dealer_Competitor_Agency] e ON LOWER(r.Mail) = LOWER(e.MAIL)
LEFT JOIN [7011G000000OJtMQAW_CI_MR_SIS2GO_Welcome_sendable] s ON LOWER(r.Mail) = LOWER(s.EmailAddress)
LEFT JOIN ENT.Embargoed_Countries ec ON s.Mailing_Country__c = ec.Mailing_Country__c


WHERE
    e.Mail IS NULL
    AND r.Mail IS NOT NULL
    AND s.SubscriberKey IS NULL
    AND ec.Mailing_Country__c IS NULL
    ) t2
    
    WHERE RowNum = 1