SQLize Online / PHPize Online  /  SQLtest Online

select SubscriberKey, EmailAddress, firstname, lastname, FacilityID, SubscriptionFacilityName, IsActiveFacility, OffersFresh, OffersPackage, OffersPersonal, Email_OptIn, PhoneNumber, SMS_OptIn, SMS_Locale, RefreshDate, Segment from ( select SubscriberKey, EmailAddress, firstname, lastname, FacilityID, SubscriptionFacilityName, IsActiveFacility, OffersFresh, OffersPackage, OffersPersonal, Email_OptIn, PhoneNumber, SMS_OptIn, SMS_Locale, RefreshDate, Segment, row_number() over (partition by EmailAddress order by Segment) as rnk from ( select SubscriberKey, EmailAddress, firstname, lastname, t1.FacilityID, f.SubscriptionFacilityName, f.inmatesearchableflag as IsActiveFacility, f.OffersFreshCategoryFlag as OffersFresh, f.OffersPackageCategoryFlag as OffersPackage, f.OffersPersonalCareCategoryFlag as OffersPersonal, Email_OptIn, PhoneNumber, SMS_OptIn, SMS_Locale, RefreshDate, Segment from ( select c.ID as SubscriberKey, c.email as EmailAddress, c.firstname, c.lastname, case when c.MC_iCare_Facility_Shortname__c is null and o.Facility is not null then o.Facility else c.MC_iCare_Facility_Shortname__c end as FacilityID, c.MC_Email_Subscription_Status__c as Email_OptIn, c.MC_SMS_Phone_Number__c as PhoneNumber, c.MC_SMS_Subscription_Status__c as SMS_OptIn, 'en-us' as SMS_Locale, GetDate() as RefreshDate, 'A' as Segment from Contact_Salesforce_1 c left join iCareUS_Facilities f on c.MC_iCare_Facility_Shortname__c = f.storeid left join iCareUS_Orders o on lower(c.email) = lower(o.Customer_Email_Address) where c.MC_sync_to_iCaremkt__c = 1 and c.MC_Email_Subscription_Status__c = 1 and c.email is not null ) t1 left join iCareUS_Facilities f on t1.FacilityID = f.storeid left join iCareUS_Product_Catalog p on f.storeid = p.FacilityList where f.inmatesearchableflag = 1 and upper(p.ProductName) LIKE 'Slam Dunk Snacks' UNION select SubscriberKey, EmailAddress, firstname, lastname, t1.FacilityID, f.SubscriptionFacilityName, f.inmatesearchableflag as IsActiveFacility, f.OffersFreshCategoryFlag as OffersFresh, f.OffersPackageCategoryFlag as OffersPackage, f.OffersPersonalCareCategoryFlag as OffersPersonal, Email_OptIn, PhoneNumber, SMS_OptIn, SMS_Locale, RefreshDate, Segment from ( select c.ID as SubscriberKey, c.email as EmailAddress, c.firstname, c.lastname, case when c.MC_iCare_Facility_Shortname__c is null and o.Facility is not null then o.Facility else c.MC_iCare_Facility_Shortname__c end as FacilityID, c.MC_Email_Subscription_Status__c as Email_OptIn, c.MC_SMS_Phone_Number__c as PhoneNumber, c.MC_SMS_Subscription_Status__c as SMS_OptIn, 'en-us' as SMS_Locale, GetDate() as RefreshDate, 'B' as Segment from Contact_Salesforce_1 c left join iCareUS_Facilities f on c.MC_iCare_Facility_Shortname__c = f.storeid left join iCareUS_Orders o on lower(c.email) = lower(o.Customer_Email_Address) where c.MC_sync_to_iCaremkt__c = 1 and c.MC_Email_Subscription_Status__c = 1 and c.email is not null ) t1 left join iCareUS_Facilities f on t1.FacilityID = f.storeid left join iCareUS_Product_Catalog p on f.storeid = p.FacilityList where f.inmatesearchableflag = 1 and f.OffersPackageCategoryFlag = 1 UNION select SubscriberKey, EmailAddress, firstname, lastname, t1.FacilityID, f.SubscriptionFacilityName, f.inmatesearchableflag as IsActiveFacility, f.OffersFreshCategoryFlag as OffersFresh, f.OffersPackageCategoryFlag as OffersPackage, f.OffersPersonalCareCategoryFlag as OffersPersonal, Email_OptIn, PhoneNumber, SMS_OptIn, SMS_Locale, RefreshDate, Segment from ( select c.ID as SubscriberKey, c.email as EmailAddress, c.firstname, c.lastname, case when c.MC_iCare_Facility_Shortname__c is null and o.Facility is not null then o.Facility else c.MC_iCare_Facility_Shortname__c end as FacilityID, c.MC_Email_Subscription_Status__c as Email_OptIn, c.MC_SMS_Phone_Number__c as PhoneNumber, c.MC_SMS_Subscription_Status__c as SMS_OptIn, 'en-us' as SMS_Locale, GetDate() as RefreshDate, 'C' as Segment from Contact_Salesforce_1 c left join iCareUS_Facilities f on c.MC_iCare_Facility_Shortname__c = f.storeid left join iCareUS_Orders o on lower(c.email) = lower(o.Customer_Email_Address) where c.MC_sync_to_iCaremkt__c = 1 and c.MC_Email_Subscription_Status__c = 1 and c.email is not null ) t1 left join iCareUS_Facilities f on t1.FacilityID = f.storeid left join iCareUS_Product_Catalog p on f.storeid = p.FacilityList where f.inmatesearchableflag = 1 and f.OffersFreshCategoryFlag = 1 ) x ) y where rnk = 1 and exists (select a.SubscriberKey from [A00 90 Days] a where a.SubscriberKey = y.SubscriberKey)