SELECT ContactId, InteractionId, Business_Unit__c, Company_Name__c, FirstName, LastName, Email, Domain, MobilePhone, MailingCity, MailingPostalCode, MailingCountry, MailingCountryCode, InteractionCreatedDate, Cat_Campaign_Most_Recent__c, AMC_Last_Activity_Date__c FROM ( SELECT c.Id AS ContactId, i.Id AS InteractionId, i.Business_Unit__c, i.Company_Name__c, c.FirstName, c.LastName, c.Email, SUBSTRING (c.Email, CHARINDEX( '@', c.Email) + 1, LEN(c.Email)) AS Domain, c.MobilePhone, c.MailingCity, c.MailingPostalCode, c.MailingCountry, c.MailingCountryCode, i.CreatedDate AS InteractionCreatedDate, i.Cat_Campaign_Most_Recent__c, c.AMC_Last_Activity_Date__c, ROW_NUMBER() OVER (PARTITION BY c.Id ORDER BY i.CreatedDate DESC) AS RowNumber FROM ENT.Interaction__c_Salesforce i INNER JOIN ENT.Contact_Salesforce_1 c ON i.Caterpillar_Contact__c = c.Id WHERE c.GTS_Screen_Status__c = 'Released' AND c.Email IS NOT NULL AND SUBSTRING (c.Email, CHARINDEX( '@', c.Email) + 1, LEN(c.Email)) NOT IN ('cat.com', 'perkins.com', 'solarturbines.com', 'perficient.com', 'deloitte.com', 'simantel.com', 'marketone.com', 'gelia.com') AND c.MailingCountryCode = 'US' AND (i.Mailing_Country__c = 'US' AND c.Consent_Level_Summary__c in ('Express Consent' , 'Validated Consent', 'Legacy Consent')) AND c.AMC_Last_Activity_Date__c BETWEEN DATEADD(month, -36, GETDATE()) AND DATEADD(month, -18, GETDATE()) AND c.AMC_Last_Activity_Record_ID__c <> 'Not Marketable' ) AS "Contacts" WHERE RowNumber = 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