1

We have multiple newsletters that generate leads for marketing emails. I'm trying to automate the process of consolidating those leads into one master data extension after they have been on a newsletter list for 30 days.

Currently, the sql I'm using looks like this:

SELECT 
           L.CreatedDate
          ,L.EmailAddress
          ,L.ListID
          ,L.Status
FROM ent._ListSubscribers L
where l.ListID   in  (20798913, 20757098, 20828774, 20233021, 20551644, 18973750, 20549665, 20540177, 20712964, 20233025, 19009581, 19009580, 20427611, 19649300, 20233035, 20585536, 20468748, 19009576, 19009574, 19009571, 19009570) /*All_Pub_Lists*/
and
Status   =  'Active'
and
CreatedDate between (dateAdd(dd, -31, dateAdd(dd, 0, getDate()))) and (dateAdd(dd, -30, dateAdd(dd, 0, getDate())))
and
l.EmailAddress not in (select EmailAddress from ent.Master_Prospect_Copy)

The query activity is set to append new data in the target data extension. I was able to run the query a single time and it produced the expected results. However, now whenever the query activity runs it fails due to a Duplicate Primary Key error.

The error makes sense, as people are free to sign up to newsletters whenever they'd like, so they end up on multiple lists with multiple created dates. I tried to removed the duplicates with the 

and
l.EmailAddress not in (select EmailAddress from ent.Master_Prospect_Copy)

but it hasn't worked.

Does anyone know how I might continue to append new data, but avoid triggering the Duplicate Primary Key error?

Best, Jeff

Improve this question
10
  • Is email address your primary key?
    – 0xsegfault
    Jun 16, 2017 at 18:35
  • You would need a sub query that collects all the contacts, a row number over by partition to group them and an additional WHERE condition to for where Row > 1
    – 0xsegfault
    Jun 16, 2017 at 18:42
  • Email address is my primary key, apologies for not specifying. I'll give your suggestion a shot, thanks!
    – Jeff Ayers
    Jun 16, 2017 at 18:46
  • No need for the sub query
    – 0xsegfault
    Jun 16, 2017 at 18:48
  • 1
    But thanks for all of the help!
    – Jeff Ayers
    Jul 17, 2017 at 16:51

1 Answer 1

Reset to default
1 
Because you are only interested in distinct leads I would use this:


SELECT 
           Row_Number() Over(PARTITION BY EmailAddress ORDER BY CreatedDate DESC) AS Row
           ,L.CreatedDate
          ,L.EmailAddress
          ,L.ListID
          ,L.Status
FROM ent._ListSubscribers L
where l.ListID   in  (20798913, 20757098, 20828774, 20233021, 20551644, 18973750, 20549665, 20540177, 20712964, 20233025, 19009581, 19009580, 20427611, 19649300, 20233035, 20585536, 20468748, 19009576, 19009574, 19009571, 19009570) /*All_Pub_Lists*/
and
Status   =  'Active'
and
CreatedDate between (dateAdd(dd, -31, dateAdd(dd, 0, getDate()))) and (dateAdd(dd, -30, dateAdd(dd, 0, getDate())))
and
Row = 1

Should solve it

Improve this answer
2
  • 1
    Sorry about the formatting can't get it write on my phone
    – 0xsegfault
    Jun 16, 2017 at 18:48
  • Also you don't need the aliases
    – 0xsegfault
    Jun 16, 2017 at 19:28

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.