I need to allow entrants into a journey in Journey Builder multiple times depending on the country of their booking and then preserve the entry and exit dates at a country level.
I have created a concatenated key in the sql query and set it as the primary key on the de. The GETDATE() should ensure it's uniqueness for each run of the query, which i then plan to use to match and isolate the specific exit dates using another query.
I normally use Row_Number to deduplicate and it works fine but in this case it is ignoring the country and keeps returning 2 rows both with the same country and a row_number of 1.
I have tried ordering by different things as i don't have a unique date, but the result is the same each time.
Query is below with comments.This SQL is for use in Marketing Cloud Automation Studio.
SELECT /*Construct the unique key*/ a.*, Concat(a.ContactKey,'_',a.Country,'_',GETDATE()) as CompositeKey FROM (SELECT ids.ETA, ids.CUSTOMER_EMAIL as Email, ids.Shipment_Count, ids.Dep_FFE, ids.Port, ids.Country, /*Create Hashed Email*/ CONVERT(NVARCHAR(MAX), HASHBYTES('SHA2_256', CAST(ids.CUSTOMER_EMAIL as varchar(64))), 2) as Email_Hash, cmm.ContactKey, cmm.External_ID__c, /*Deduplicate records by contact id*/ ROW_NUMBER() OVER (PARTITION BY ids.CUSTOMER_EMAIL, ids.Country ORDER BY ids.CUSTOMER_EMAIL DESC) AS RowNumber, /*Set English as default*/ CASE WHEN cmm.Preferred_Language__c IS NOT NULL THEN cmm.Preferred_Language__c ELSE 'English' END as Preferred_Language__c FROM Table1 as ids INNER JOIN Table2 as cmm on ids.Customer_Email = cmm.Email WHERE ids.Customer_Email IS NOT NULL) as a LEFT JOIN Table4 as je on a.ContactKey = je.ContactKey and a.Country = je.Country LEFT JOIN Table5 as cg on a.ContactKey = cg.ContactKey and a.Country = cg.Country WHERE a.RowNumber = 1 /*Check record is not already in Journey Entry or Control Group*/ AND (je.Country IS NULL or je.Journey_Exit_Date < DATEADD(minute,-10,GETDATE())) AND (cg.Country IS NULL or cg.DateAdded_To_ControlGroup < DATEADD(minute,-10,GETDATE()))`/