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
Preview:
downloadDownload PNG
downloadDownload JPEG
downloadDownload SVG
Tip: You can change the style, width & colours of the snippet with the inspect tool before clicking Download!
Click to optimize width for Twitter