enter image description hereHow to Avoid The Duplicate Subscriber Key in Query Activtiy this my Query

DISTINCT s.SubscriberKey,
s.BounceCategory as BounceType,
(CONVERT(varchar,s.JobID)+CONVERT(varchar,s.SubscriberKey)) as UID
from _Bounce as s 
where s.BounceCategory = 'Hard Bounce' and s.IsUnique='true'

I am getting duplicate values

  • 1
    What is the goal of your query? What are the primary keys in your target Data Extension? Please provide more background information on this request. – Gortonington Jul 12 '17 at 13:05
  • In My Dataextension primary key is SubscriberKey and UID – user C Jul 12 '17 at 13:08

The easiest way to deduplicate this is to set only the subscriberkey as the primary key. This will cause all duplicate values to just 'update' the existing one.

By having the two primary keys, you open up the opportunity for multiple iterations of the Subscriberkey if the UID is different.

I am not sure your use case for this information, but if you are just looking for a count of unique subscribers that bounced in general, this can be achieved as stated above.

BUT, if you are looking for unique bounces per Job, then using your UID or using subscriberkey and JobID as primary will provide this (like what you currently have with multiple instances of subscriberkey).

If you can provide more detail into your overall goal, I can edit my answer to make it more specific to your need.

| improve this answer | |

I'd suggest de-duplicating with a windowing function. Something like this:

, x.JobID
, x.BounceCategory as BounceType
, x.SubscriberID
, x.IsUnique
, x.uid
from (
    , b.JobID
    , b.BounceCategory as BounceType
    , b.SubscriberID
    , b.IsUnique
    , (CONVERT(varchar,b.JobID) + CONVERT(varchar,b.SubscriberKey)) as UID
    , row_number() over(partition by b.SubscriberKey, b.jobid order by b.eventDate) ranking
  from _bounce b
  b.BounceCategory = 'Hard Bounce' 
  and b.IsUnique = 1
) x
where x.ranking = 1

This assigns a number to each of the _bounce rows by subscriberkey and jobid ordered by date. The x.ranking = 1 picks the first one for each subscriber.

| improve this answer | |

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.