query.sql

SELECT
  subs."Hashed_Email" hashed_email,
  T."subscriber_id",
  pvc."VariationCode" variation_code,
  pvc."GenopediaID" geno_id,
  pvc."CampaignName",
  pvc."JobID"
 FROM PersadoClassicResults AS subs
 left outer join PersadoVariationCodes AS pvc 
    ON pvc."CampaignName" = subs."Campaign_Name"
    AND  pvc."JobID" = subs."Send_ID"
    AND  pvc."GenopediaID" = subs."Subject_Line"
  left outer join (
    SELECT
    CASE
    WHEN CHARINDEX('+', emailaddress) = 0
    THEN CONVERT(varchar(32), HashBytes('MD5', CONVERT(VARCHAR(32), LOWER(emailaddress), 2)), 2)
    ELSE CONVERT(varchar(32), HashBytes('MD5', CONVERT(VARCHAR(32), LOWER(STUFF(emailaddress,
    CHARINDEX('+', emailaddress),
    (CHARINDEX('@', emailaddress) - CHARINDEX('+', emailaddress)), '')), 2)), 2)
    END AS hashed_email_address,
    SubscriberID subscriber_id
    FROM _Subscribers
  ) as T on T.hashed_email_address = subs."Hashed_Email"