1

I'm trying to make a query that checks to see if a user's value for [PaperlessBillingOptIn] is equal to "N" for a promotional email for a new feature.

However, because users can have multiple records in our database (attached to their policies), they may sometimes have one record with "Y" and one with "N."

In this case, we don't want to send to a user whose PaperlessBillingOptIn is "N" if they have another record for which it is "Y."

How can this be accomplished within an Automation Studio query activity? It seems like doing normal counts doesn't work here.

Improve this question
2

You could do something like this:

select distinct
  m.emailAddress
, m.PaperlessBillingOptIn
from Policies_Master m
where m.PaperlessBillingOptIn = 'N'
and not exists (
  select *
  from Policies_Master m2
  where m2.emailAddress = m.emailaddress
  and m2.PaperlessBillingOptIn = 'Y'
)
Improve this answer
4
  • I had thought to try something like this at first, but the Automation Studio queries don't support 'select distinct' – jessed Apr 15 '18 at 14:35
  • You can select distinct in SFMC queries. – Adam Spriggs Apr 15 '18 at 18:01
  • Ah. I had read somewhere that it didn't work. In any event, when I replicate the above, I get the error: "The multi-part identifier "m2.PaperlessBillingOptIn" could not be bound." Is there an additional step here? – jessed Apr 16 '18 at 12:45
  • Did you update the query to use the actual name of your data extension? You didn't specify it in your question. – Adam Spriggs Apr 16 '18 at 13:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.