May 4, 2018 at 4:39 am
webskater - Wednesday, May 2, 2018 12:24 PM
CREATE TABLE #tblContacts
(
ContactID int,
Contact varchar(100)
)GO
INSERT INTO #tblContacts (ContactID, Contact)
SELECT 1, 'Fred' UNION ALL
SELECT 2, 'Jim' UNION ALL
SELECT 3, 'Arthur'GO
CREATE TABLE #tblEmails
(
EmailID int,
ContactID int,
EmailAddress varchar(100)
)GO
INSERT INTO #tblEmails (EmailID, ContactID, EmailAddress)
SELECT 1, 1, 'fred@someorg.cam' UNION ALL
SELECT 2, 2, 'jim@goggle.cam' UNION ALL
SELECT 3, 3, 'Arthur@giggle.cam'CREATE TABLE #tblNoEmail
(
EmailAddress varchar(100)
)INSERT INTO #tblNoEmail(EmailAddress)
SELECT 'jim@goggle.cam'GO
SELECT * FROM #tblContacts
INNER JOIN #tblEmails On #tblContacts.ContactID = #tblEmails.ContactID
WHERE #tblEmails.EmailAddress NOT IN (SELECT EmailAddress FROM #tblNoEmail)GO
DROP TABLE #tblContacts
DROP TABLE #tblEmails
DROP TABLE #tblNoEmailJim has told us he does not want to be contacted by us but, for historical reasons, his contact record cannot be deleted. There are many dozens of places within a number of applications used by a number of different teams that can send an email to a list of people. I need to make sure no email is ever sent to his email address. I can't simply put a flag on the #tblEmails for reasons I won't bore you with. Apart from those reasons, I have been told to create #tblNoEmail and maintain a list of email addresses that must never be contacted. Whenever any email is sent from any of our applications (and there are about 20 teams using their own project planning systems over the same database) - I have to check any email address we are about to send an email to is not in 'tblNoEmail'
Something like ....
SELECT ContactID, Contact, EmailAddress
FROM #tblContacts
INNER JOIN #tblEmails On #tblContacts.ContactID = #tblEmails.ContactID
WHERE #tblEmails.EmailAddress NOT IN (SELECT EmailAddress FROM #tblNoEmail)should do it (although there is a syntax error in my code above that I can't find and that statement above will not run for some reason ? ). But, given there are 200,000 contact records, about 500,000 records in #tblEmails and will be a few thousand in #tblNoEmail - what's the most efficient way of always making sure that any email in a list of email addresses I am about to send an email to will not contain any of the email addresses in #tblNoEmail
FYI this is called a suppression list.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 4, 2018 at 5:57 am
Sergiy - Friday, May 4, 2018 4:26 AMHave a DistributionList:ContactIdMediaId (lookup for email, post address, phone, messangers, etc.)(?) ExpiryDate
We have hundreds of them. Based on team and category. But, under these new GDPR rules, we have to make sure that anyone who contacts us and says 'don't contact me again' must never receive an email from us again. In a sense the NoEmail is a master 'don't contact me' list.
May 4, 2018 at 7:45 am
Just remove the record from the distribution list.
Keep all the contact details intact.
_____________
Code for TallyGenerator
May 4, 2018 at 8:12 am
ChrisM@Work - Friday, May 4, 2018 4:37 AMSQL Server will apply filters and joins in whatever order is calculated to be most efficient. In the example above, moving the filter from the where clause to the join is unlikely to change the execution plan.
I believe that there's a LEFT missing on the JOIN. Moving the filter will change the results when an OUTER JOIN is used (I'm sure you know that, but I just wanted to keep it clear).
May 4, 2018 at 8:24 am
Sergiy - Friday, May 4, 2018 7:45 AMJust remove the record from the distribution list.Keep all the contact details intact.
Sergiy - when I've worked with mailing companies, it's been standard practice to use suppression files on any (mailing)list, partly because there are multiple types of suppressions, partly because the consequences can be expensive if people are mailed when they've opted out.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 4, 2018 at 9:44 am
I would lean towards putting the PK and the email in the #NoEMail list. Then I'd put a token in the main list, as mentioned previously, because someone or a new process might look to email everyone on the list without checking #NoEmail.
May 4, 2018 at 3:04 pm
> been standard practice to use suppression files on any (mailing)list, partly because there are multiple types of suppressions, partly because the consequences can be expensive if people are mailed when they've opted out.
It's actually serves the opposite.
I've seen an actual occasion when an overnight process of building the suppression list failed (blown up the tempdb) and early morning emails went out with no suppressions applied at all.
Think not "all excluding suppressed", think "all that must be emailed".
_____________
Code for TallyGenerator
May 4, 2018 at 3:07 pm
Sergiy - Friday, May 4, 2018 3:04 PM> been standard practice to use suppression files on any (mailing)list, partly because there are multiple types of suppressions, partly because the consequences can be expensive if people are mailed when they've opted out.It's actually serves the opposite.I've seen an actual occasion when an overnight process of building the suppression list failed (blown up the tempdb) and early morning emails went out with no suppressions applied at all.Think not "all excluding suppressed", think "all that must be emailed".
Well usually you would do both, generate the list you want to send out, them compare it to anything that definitely shouldn't be sent. If that process is blowing something up then that needs to be fixed.
May 4, 2018 at 6:45 pm
No, I'd usually compare to what should not be sent WHILE generating the list to be sent.
And those which not to be sent never appear in the list to be sent.
So, no mistake in the emailing procedure can send emails to somebody who's not supposed to receive it.
Isn't it a bit safer that relying on suppression lists?
_____________
Code for TallyGenerator
May 4, 2018 at 7:51 pm
There is a fundamental error in the design described in the op.
Every customer with an email address is considered a recipient of emails.
Except those ones which are excluded.
Having an email registered in the database should not mean receiving an email.
To receive emails a customer's email must be registered in a certain distribution list.
Which is populated according to whatever business rules are in place: "All except opted out", "having no activity last month", etc.
And population/modification of such list(s) happens asynchronously, on it's own time, so the emailing procedure can do what it's meant to do - sending out emails, not working out multiple confusing business rules for the list of recipients.
_____________
Code for TallyGenerator
May 5, 2018 at 12:29 pm
I'd agree with Sergiy here. We always used a generation process to produce a list here, so that changes, exceptions, etc. could be applied. We never used the original table as the source for actual sending.
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply