Timeline for How to reproduce unique record based on a field value (date, account code) on data extension
Current License: CC BY-SA 4.0
12 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Apr 28, 2022 at 11:22 | comment | added | LT888 | @Alex, thanks a lot it worked ! | |
| Apr 28, 2022 at 11:04 | comment | added | Alex |
@LT888 updated the answer above, converting datetime into date, you can also add this to the SELECT statement if you want to summarise on dates rather than datetime. In your last comment, your query had weird formatting and CodeGie was together with FROM, make sure to double check the formatting :)
|
|
| Apr 28, 2022 at 11:01 | history | edited | Alex | CC BY-SA 4.0 |
edited after more information about query
|
| Apr 28, 2022 at 9:54 | comment | added | LT888 |
@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
|
|
| Apr 28, 2022 at 9:38 | vote | accept | LT888 | ||
| Apr 28, 2022 at 9:38 | comment | added | LT888 |
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.
|
|
| Apr 28, 2022 at 9:25 | vote | accept | LT888 | ||
| Apr 28, 2022 at 9:35 | |||||
| Apr 27, 2022 at 16:02 | comment | added | Adam Spriggs | Don't use Query Studio. | |
| Apr 27, 2022 at 15:47 | comment | added | LT888 |
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())
|
|
| Apr 26, 2022 at 19:28 | history | edited | Alex | CC BY-SA 4.0 |
Updated per adams comment
|
| Apr 26, 2022 at 19:23 | comment | added | Adam Spriggs | This won't work without adding an outer query. You could use the shorthand version of this method: sprignaturemoves.com/picking-rows-with-partitions | |
| Apr 26, 2022 at 19:14 | history | answered | Alex | CC BY-SA 4.0 |