Dennis
11/30/2018 - 10:49 AM

FP - SQL 1

Selecteert de laatste status van subscribers en wanneer gevuld ook de nawte uit IMPORT ALL. De datum moet groter zijn dan die er al in staat OF het emailadres bestaat nog niet in die data extensie.

NEW Import unique subscribers.sql
content_copyfile_download
SELECT a.EmailAddress,
CASE WHEN a.Firstname IS NOT NULL THEN a.Firstname END AS Firstname,
CASE WHEN a.Lastname IS NOT NULL THEN a.Lastname END AS Lastname,
CASE WHEN a.Gender IS NOT NULL THEN a.Gender END AS Gender,
a.date AS Modified
FROM [Import - all] a
LEFT JOIN [IMPORT Unique Subscribers] b ON a.EmailAddress = b.EmailAddress 
INNER JOIN 
  (SELECT EmailAddress,
    max(date) as date
  FROM [Import - all] 
  GROUP BY EmailAddress 
) AS c
  ON a.EmailAddress = c.EmailAddress 
  AND a.date = c.date
WHERE b.modified < a.date OR b.EmailAddress IS NULL