2

I'm currently trying to find a list of email Addresses with a particular date in a data extension. The data extension can contain multiple instances of the same email address, but with different dates (the dates refer to campaigns that these people have booked). I'm using the below query.

SELECT emailAddress, creativeDate
FROM CC_B2B_Email A 
WHERE (creativeDate = DATEADD(Day,49,CAST(GETDATE() AS date)))
AND creativeReceived = 'False' 
AND Not exists (Select 1 FROM CC_B2B_Display B WHERE b.emailAddress = a.emailAddress)

The final two AND clauses aren't important so you can ignore those.

We're using this to send a daily email. I would like to refine this search so it retrieves only those where the MINIMUM value of creative Date found for them meets the WHERE criteria.

So if a person appears in there twice, with 16/08/2018 and 17/08/2018, I only want them to appear in there once - when 16/08/2018 is 49 days away, but not when 17/08/2018 is 49 days away.

When I try to run

SELECT emailAddress, MIN(1)creativeDate
FROM CC_B2B_Email A 
WHERE (creativeDate = DATEADD(Day,49,CAST(GETDATE() AS date)))
AND creativeReceived = 'False' 
AND Not exists (Select 1 FROM CC_B2B_Display B WHERE b.emailAddress = a.emailAddress)

I get an error saying emailAddress is not in an aggregate function. Can anyone help?

  • 1
    group by EmailAddress? – Samuel Dare Jun 15 '18 at 11:43
  • 1
    Thanks - that worked! I'd forgotten you have to group by. – TCassa Jun 15 '18 at 13:11
3

When you use an agregate function (e.g COUNT, MIN, AVERAGE, MAX), you have to finish the statement with a GROUP BY clause and include any column that is not agregrated.

In your case it would be

SELECT emailAddress, MIN(1)creativeDate
FROM CC_B2B_Email A 
WHERE (creativeDate = DATEADD(Day,49,CAST(GETDATE() AS date)))
AND creativeReceived = 'False' 
AND Not exists (Select 1 FROM CC_B2B_Display B WHERE b.emailAddress = a.emailAddress)
GROUP BY emailAddress

Reference:

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.