0

I wanted to know if MAX(Eventdate) works in SFMC as its not working for me. I'm trying to only extract customer who have opened the email and only want to pull the records with most recent time/date as the extract contains dupes.

The query I'm using is below. I only want to pull all customers who has opened their email yesterday and with the email name below

SELECT 
    Max(EventDate) as Eventdate,
    IsUnique,
    SubscriberKey,
    SubscriberID 
FROM _open o
JOIN _Job j
    ON j.jobid = o.jobid 
WHERE eventdate >= DATEADD(dd,-1,getdate())
    AND EmailName = 'EW01 welcome email'
2
  • Yes MAX works. You need to group by IsUnique, SubscriberKey,SusbcriberID – Samuel Dare Aug 18 '17 at 20:05
  • How is it not working? Is there an error message? – Samuel Dare Aug 18 '17 at 20:08
0

Answer

Yes it does work. For a list of some functions available in SFMC visit : SQL Queries.

As you havent provided your error, I suspect its not working because you havent used the GROUP BY clause. Whenever you use aggregate values, you have to group by everything else that isnt aggregated.

Try this:

SELECT 
    Max(EventDate) as Eventdate,
    IsUnique,
    SubscriberKey,
    SubscriberID 
FROM _open o
JOIN _Job j
    ON j.jobid = o.jobid
WHERE eventdate >= DATEADD(dd,-1,getdate())
    AND EmailName = 'EW01 welcome email'
GROUP BY IsUnique, SubscriberKey,SubscriberID `

UPDATE

From your comments , I advise you not to use MAX and use the query below instead.

SELECT 
    Eventdate,
    IsUnique,
    SubscriberKey,
    SubscriberID
FROM 
    (SELECT 
        Eventdate,
        row_number()over(partition by SubscriberKey order by eventdate asc) as row
        IsUnique,
        SubscriberKey,
        SubscriberID 
    FROM _open o
    JOIN _Job j
        ON j.jobid = o.jobid 
    WHERE eventdate >= DATEADD(dd,-1,getdate())
        AND EmailName = 'EW01 welcome email'
    GROUP BY
        IsUnique,
        SubscriberKey,
        SubscriberID
    ) x
WHERE x.row = 1

A bit more lines of code but gets the job done and is by far my prepared method of deduping by order

3
  • the query runs but it still showing dupes. for example 18 August 2017 12:32 And 18 August 2017 04:05. it should only show the 1st record and not the second – Peter Ho Aug 18 '17 at 20:53
  • Ah...you didnt say what the problem was updating my query now – Samuel Dare Aug 18 '17 at 21:03
  • @PeterHo pleasure – Samuel Dare Aug 18 '17 at 22:50

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.