0

Lets say I have rows of sends on contacts on a data extension. These contacts can have the same value on the column Event Date, name and account code

This data extension would be like that :

Contact ID      EmailName         Date                AccountCode 
123             Email1            24/04/2022          12
124             Email1            24/04/2022          12
125             Email1            25/04/2022          12
126             Email2            25/04/2022          12
135             Email2            25/04/2022          13
136             Email2            26/04/2022          13
137             Email3            26/04/2022          13
138             Email3            27/04/2022          13
139             Email3            27/04/2022          13

I would like to deduplicate the rows to display one row (the first one) per EmailName, Date, and AccountCode.

The results would be like that :

Contact ID      EmailName         Date                AccountCode 
123             Email1            24/04/2022          12
125             Email1            25/04/2022          12
126             Email2            25/04/2022          12
135             Email2            25/04/2022          13
136             Email2            26/04/2022          13
137             Email3            26/04/2022          13
139             Email3            27/04/2022          13
Improve this question
3
  • Do you want to implement this logic in Apex?
    – Sneh
    Commented Apr 26, 2022 at 17:55
  • @Sneh, not it is to update a data extension in MarketingCloud
    – LT888
    Commented Apr 26, 2022 at 17:56
  • Have you tried writing the query @LT888? If so, please update your question to include your attempt.
    – Adam Spriggs
    Commented Apr 26, 2022 at 19:22

1 Answer 1

Reset to default
1

There might exist more optimal ways to do this but i always return to this for these type of filtering.

SELECT TOP 1 WITH ties 
    [SubscriberKey], 
    EmailName,
    [EventDate],
    CodeGie
FROM [Reporting]
ORDER BY ROW_NUMBER() OVER (
    PARTITION BY EmailName, CodeGie, CONVERT(DATE, [EventDate]) 
    ORDER BY CONVERT(DATE, [EventDate]) ASC)

Edit: Edited per Adams comment below & with more info about query.

Improve this answer
7
  • 1
    This won't work without adding an outer query. You could use the shorthand version of this method: sprignaturemoves.com/picking-rows-with-partitions
    – Adam Spriggs
    Commented Apr 26, 2022 at 19:23
  • Hi @AdamSpriggs, I tried this code but it didn't work. I do have the error in QueryStudio "Incorrect syntax near the keyword 'with'.This is the code I tried select top 1 with ties r.SubscriberKey r.EventDate , r.CodeGIE , r.EmailName from Reporting order by row_number() over (partition by r.EmailName, r.EventDate order by date())
    – LT888
    Commented Apr 27, 2022 at 15:47
  • Don't use Query Studio.
    – Adam Spriggs
    Commented Apr 27, 2022 at 16:02
  • Hi @Adam Spriggs, I have put this code in the automation and it seemed to work : SELECT TOP 1 WITH ties [SubscriberKey], EmailName, [EventDate], CodeGie FROM [Reporting] x ORDER BY ROW_NUMBER() OVER (PARTITION BY EmailName, CodeGie, [EventDate] ORDER BY [EventDate] ASC). Thanks again for your help.
    – LT888
    Commented Apr 28, 2022 at 9:38
  • @AdamSpriggs I have put this code in the automation but I still have multiple rows for the same EventDate : SELECT TOP 1 WITH ties [SubscriberKey], EmailName,[EventDate], CodeGieFROM [Reporting] x ORDER BY ROW_NUMBER() OVER (PARTITION BY EmailName, CodeGie, [EventDate] ORDER BY [EventDate] ASC). The issue is that my EventDate is with the format Month/Day/Year and Time Stamp like this Apr 25 2022 5:11PM.If I have two different times in the day for the same Code Gie, it will returns two rows. I think I have to change the format on a new field and change the EventDate in my request
    – LT888
    Commented Apr 28, 2022 at 9:54

Not the answer you're looking for? Browse other questions tagged .