Check for existing records in Data Extension when appending data

Question

We're sending anniversary mails to members every time they've lasted another year with us :) This is the SOQL appending data to the Data Extension [Anniversary Flow - Trigger]

SELECT
ID as 'Subscriber Key',
CreatedDate,
CASE WHEN Email IS NULL THEN '[email protected]'
ELSE EMAIL
END AS EMAIL,
Country,
Segmentation,
locale,
DATEPART(year, GETDATE()) - DATEPART(year, CreatedDate) as 'Anniversary years'
From Subscribers
WHERE DATEPART(day, DATEADD(HOUR, 7, GETDATE())) = DATEPART(day, CreatedDate) AND
DATEPART(month, GETDATE()) = DATEPART(month, CreatedDate) 
AND (DATEPART(year, GETDATE()) - DATEPART(year, CreatedDate)) > 0

This has worked perfectly until now. The Data Extension has 2 primary keys: [Subscriber Key] and [Anniversary years]

We've recently had an example of a subscriber having the CreatedDate renewed, causing the automation appending data to the Data Extension [Anniversary Flow - Trigger] to error with a 'Violation of PRIMARY KEY constraint' as that member was now starting all over on her anniversary years and for that reason already existed in the Data Extension.

How can I alter the SQL above to not include subscribers if they're already existing in the [Anniversary Flow - Trigger] with that combination of primary keys

And yes, I'm aware that this is not solving all my problems, but it'd be really helpful here and now and a perfect temporary solution.

Thank you

Answer 1

When trying to rewrite my SQL I found that I could easily do so, if the result of the SQL was adding and updating to the Data Extension, not appending to it. As this was not an option in this scenario, I had to go with this solution instead:

To fix the issue we went with this solution:

• Copy the existing [Anniversary Flow - Trigger] DE into a log DE
• When querying for new anniversaries also check if they exist in the log DE. Those that exist (with that combination of primary keys) in the log DE are subscribers with renewed CreatedDates, and will not be added to the [Anniversary Flow - Trigger] DE
• Those that doesn't exist will be added to the [Anniversary Flow - Trigger] DE and will have their anniversary vouchers
• Create an extra SQL step after the send of anniversary vouchers updating the log DE before next run

This is the updated SQL appending to the [Anniversary Flow - Trigger] Data Extension

SELECT
sub.ID as 'Subscriber Key',
sub.CreatedDate,
CASE WHEN sub.Email IS NULL THEN '[email protected]'
ELSE sub.EMAIL
END AS EMAIL,
sub.Country,
sub.Segmentation,
sub.locale,
DATEPART(year, GETDATE()) - DATEPART(year, sub.CreatedDate) as 'Anniversary years'
FROM Subscribers sub
LEFT JOIN AnniversaryFlow_Trigger_LOG log
ON sub.ID = log.[Subscriber Key] AND DATEPART(year, GETDATE()) - DATEPART(year, sub.CreatedDate) = log.[Anniversary years]
WHERE log.[Subscriber Key] IS NULL
AND (DATEPART(day, DATEADD(HOUR, 7, GETDATE())) = DATEPART(day, sub.CreatedDate) AND
DATEPART(month, GETDATE()) = DATEPART(month, sub.CreatedDate) AND (DATEPART(year, GETDATE()) - DATEPART(year, sub.CreatedDate)) > 0)