3

In ExactTarget I am trying to query the total number of sends within the last 7 for each subscriber, and only store the results where the number of 'TotalSends' is greater than 0.

Here is what I have going so far:

SELECT  EmailAddress, (
  SELECT count(*) FROM _Sent p
  WHERE  p.SubscriberKey = s.EmailAddress 
  AND EventDate between DATEADD(day, -7, CURRENT_TIMESTAMP) 
  AND CURRENT_TIMESTAMP
) 
AS 'TotalSent' 
FROM Master_Subscriber_DE AS s
WHERE 'TotalSent' > 0

I run this query and get no results. If I remove that last line - It works, but I get ALL results (I only want the results where 'TotalSent' > 0)

4

I ended up getting what I needed with the code below:

SELECT count(*) AS TotalSent, p.SubscriberKey as EmailAddress
FROM _Sent p 
WHERE p.EventDate BETWEEN DATEADD(day, -7, CURRENT_TIMESTAMP) AND CURRENT_TIMESTAMP
GROUP BY p.SubscriberKey
HAVING COUNT(*) > 0
  • 2
    Looks good, but what is gained by joining Master_Subscriber_DE the only column being selected from it is EmailAddress which also exists in _Sent? – Mac Feb 19 '14 at 21:25
  • You are right Mac, nothing was gained by adding that JOIN. Thanks so much for your help :) I went ahead and edited my post above. – Michelle Feb 21 '14 at 6:00
2

Haven't test it, but something like this should work assuming all your subscriberkeys are email addresses

SELECT SubscriberKey, COUNT(SubscriberKey) as TotalSent FROM _Sent
WHERE EventDate between DATEADD(day, -7, CURRENT_TIMESTAMP) AND CURRENT_TIMESTAMP
GROUP BY SubscriberKey
HAVING COUNT(SubscriberKey) > 0
0

Try using a Having clause:

SELECT count(*) as TotalSent, s.EmailAddress FROM _Sent p
  WHERE  p.SubscriberKey = s.EmailAddress 
  AND EventDate between DATEADD(day, -7, CURRENT_TIMESTAMP) AND CURRENT_TIMESTAMP
group by s.EmailAddress
having count(*) > 0
  • Hi Mac - thanks so much for your reply. When I do as you suggested, I receive this error: The multi-part identifier "s.EmailAddress" could not be bound. – Michelle Feb 19 '14 at 16:09

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.