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
  • Is email address your primary key? – Samuel Dare Jun 16 '17 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 – Samuel Dare Jun 16 '17 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 '17 at 18:46
  • No need for the sub query – Samuel Dare Jun 16 '17 at 18:48
  • 1
    But thanks for all of the help! – Jeff Ayers Jul 17 '17 at 16:51
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
  • 1
    Sorry about the formatting can't get it write on my phone – Samuel Dare Jun 16 '17 at 18:48
  • Also you don't need the aliases – Samuel Dare Jun 16 '17 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.