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[Subscriber Key] and Anniversary years[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
