SQL Name: PROD_Unified_Email
CustomerKey: EF66EBBA-3B81-463E-A440-89FD39680C98
TargetDE: PROD_NBA_Unified_Email
UpdateType: Update
QueryText: SELECT
s.id AS activityId,
'VAE' AS emailType,
s.Account_vod__c AS accountId,
s.OwnerId AS ownerId,
d.name AS templateName,
trim(s.Subject_vod__c) AS subjectLine,
s.Account_Email_vod__c AS toEmailAddress,
s.Status_vod__c AS status,
CASE
WHEN a.Country_TPI__c = 'BG' THEN DATEADD(HOUR,6,s.Email_Sent_Date_vod__c) AT TIME ZONE 'UTC' AT TIME ZONE 'E. Europe Standard Time'
WHEN a.Country_TPI__c = 'EE' THEN DATEADD(HOUR,6,s.Email_Sent_Date_vod__c) AT TIME ZONE 'UTC' AT TIME ZONE 'E. Europe Standard Time'
WHEN a.Country_TPI__c = 'GR' THEN DATEADD(HOUR,6,s.Email_Sent_Date_vod__c) AT TIME ZONE 'UTC' AT TIME ZONE 'E. Europe Standard Time'
WHEN a.Country_TPI__c = 'LT' THEN DATEADD(HOUR,6,s.Email_Sent_Date_vod__c) AT TIME ZONE 'UTC' AT TIME ZONE 'E. Europe Standard Time'
WHEN a.Country_TPI__c = 'LV' THEN DATEADD(HOUR,6,s.Email_Sent_Date_vod__c) AT TIME ZONE 'UTC' AT TIME ZONE 'E. Europe Standard Time'
WHEN a.Country_TPI__c = 'RO' THEN DATEADD(HOUR,6,s.Email_Sent_Date_vod__c) AT TIME ZONE 'UTC' AT TIME ZONE 'E. Europe Standard Time'
WHEN a.Country_TPI__c = 'CZ' THEN DATEADD(HOUR,6,s.Email_Sent_Date_vod__c) AT TIME ZONE 'UTC' AT TIME ZONE 'E. Europe Standard Time'
WHEN a.Country_TPI__c = 'SK' THEN DATEADD(HOUR,6,s.Email_Sent_Date_vod__c) AT TIME ZONE 'UTC' AT TIME ZONE 'E. Europe Standard Time'
WHEN a.Country_TPI__c = 'AT' THEN DATEADD(HOUR,6,s.Email_Sent_Date_vod__c) AT TIME ZONE 'UTC' AT TIME ZONE 'E. Europe Standard Time'
WHEN a.Country_TPI__c = 'GB' THEN DATEADD(HOUR,6,s.Email_Sent_Date_vod__c) AT TIME ZONE 'UTC' AT TIME ZONE 'UTC'
WHEN a.Country_TPI__c = 'IE' THEN DATEADD(HOUR,6,s.Email_Sent_Date_vod__c) AT TIME ZONE 'UTC' AT TIME ZONE 'UTC'
WHEN a.Country_TPI__c = 'PT' THEN DATEADD(HOUR,6,s.Email_Sent_Date_vod__c) AT TIME ZONE 'UTC' AT TIME ZONE 'W. Europe Standard Time'
WHEN a.Country_TPI__c = 'IL' THEN DATEADD(HOUR,6,s.Email_Sent_Date_vod__c) AT TIME ZONE 'UTC' AT TIME ZONE 'Israel Standard Time'
WHEN a.Country_TPI__c = 'FI' THEN DATEADD(HOUR,6,s.Email_Sent_Date_vod__c) AT TIME ZONE 'UTC' AT TIME ZONE 'Middle East Standard Time'
WHEN a.Country_TPI__c = 'CA' THEN (s.Email_Sent_Date_vod__c)
ELSE NULL
END AS sentDate,
NULL AS campaignName,
NULL AS campaignId,
NULL AS bounceSubcategory,
a.Country_TPI__c As Country,
d.Vault_Document_ID_vod__c AS EmailId,
IIF(s.Last_Click_Date_TPI__c IS NULL, 0, 1) AS ifClicked,
CASE
WHEN a.Country_TPI__c = 'BG' THEN DATEADD(HOUR,6,s.Last_Click_Date_TPI__c) AT TIME ZONE 'UTC' AT TIME ZONE 'E. Europe Standard Time'
WHEN a.Country_TPI__c = 'EE' THEN DATEADD(HOUR,6,s.Last_Click_Date_TPI__c) AT TIME ZONE 'UTC' AT TIME ZONE 'E. Europe Standard Time'
WHEN a.Country_TPI__c = 'GR' THEN DATEADD(HOUR,6,s.Last_Click_Date_TPI__c) AT TIME ZONE 'UTC' AT TIME ZONE 'E. Europe Standard Time'
WHEN a.Country_TPI__c = 'LT' THEN DATEADD(HOUR,6,s.Last_Click_Date_TPI__c) AT TIME ZONE 'UTC' AT TIME ZONE 'E. Europe Standard Time'
WHEN a.Country_TPI__c = 'LV' THEN DATEADD(HOUR,6,s.Last_Click_Date_TPI__c) AT TIME ZONE 'UTC' AT TIME ZONE 'E. Europe Standard Time'
WHEN a.Country_TPI__c = 'RO' THEN DATEADD(HOUR,6,s.Last_Click_Date_TPI__c) AT TIME ZONE 'UTC' AT TIME ZONE 'E. Europe Standard Time'
WHEN a.Country_TPI__c = 'CZ' THEN DATEADD(HOUR,6,s.Last_Click_Date_TPI__c) AT TIME ZONE 'UTC' AT TIME ZONE 'E. Europe Standard Time'
WHEN a.Country_TPI__c = 'SK' THEN DATEADD(HOUR,6,s.Last_Click_Date_TPI__c) AT TIME ZONE 'UTC' AT TIME ZONE 'E. Europe Standard Time'
WHEN a.Country_TPI__c = 'AT' THEN DATEADD(HOUR,6,s.Last_Click_Date_TPI__c) AT TIME ZONE 'UTC' AT TIME ZONE 'E. Europe Standard Time'
WHEN a.Country_TPI__c = 'GB' THEN DATEADD(HOUR,6,s.Last_Click_Date_TPI__c) AT TIME ZONE 'UTC' AT TIME ZONE 'UTC'
WHEN a.Country_TPI__c = 'IE' THEN DATEADD(HOUR,6,s.Last_Click_Date_TPI__c) AT TIME ZONE 'UTC' AT TIME ZONE 'UTC'
WHEN a.Country_TPI__c = 'PT' THEN DATEADD(HOUR,6,s.Last_Click_Date_TPI__c) AT TIME ZONE 'UTC' AT TIME ZONE 'W. Europe Standard Time'
WHEN a.Country_TPI__c = 'IL' THEN DATEADD(HOUR,6,s.Last_Click_Date_TPI__c) AT TIME ZONE 'UTC' AT TIME ZONE 'Israel Standard Time'
WHEN a.Country_TPI__c = 'FI' THEN DATEADD(HOUR,6,s.Last_Click_Date_TPI__c) AT TIME ZONE 'UTC' AT TIME ZONE 'Middle East Standard Time'
WHEN a.Country_TPI__c = 'CA' THEN (s.Last_Click_Date_TPI__c)
END AS lastClickDate,
d.Vault_Document_ID_vod__c AS vaultDocId,
GETUTCDATE() AS lastModifiedDate
FROM
ENT.[Sent_Email_vod__c_Salesforce] s
LEFT JOIN
ENT.[Approved_Document_vod__c_Salesforce] d
ON s.Approved_Email_Template_vod__c = d.id
LEFT JOIN
ENT.[Account_Salesforce_2] a
ON s.Account_vod__c = a.id
WHERE
s.Email_Sent_Date_vod__c >= DATEADD(YEAR, -3, GETDATE())
AND a.Country_TPI__c IN ('BG', 'EE', 'GR', 'LT', 'LV', 'RO', 'GB', 'IE', 'PT', 'IL', 'FI', 'CA','CZ','SK','AT')
UNION ALL
SELECT DISTINCT
s.ActivityId AS activityId,
'SFMC' AS emailType,
s.HCP_accountid AS accountId,
NULL AS ownerId,
j.emailName AS templateName,
trim(j.EmailSubject) AS subjectLine,
a.PersonEmail AS toEmailAddress,
'Email_Sent' AS status,
CASE
WHEN a.Country_TPI__c = 'BG' THEN DATEADD(HOUR,6,s.eventDate) AT TIME ZONE 'UTC' AT TIME ZONE 'E. Europe Standard Time'
WHEN a.Country_TPI__c = 'EE' THEN DATEADD(HOUR,6,s.eventDate) AT TIME ZONE 'UTC' AT TIME ZONE 'E. Europe Standard Time'
WHEN a.Country_TPI__c = 'GR' THEN DATEADD(HOUR,6,s.eventDate) AT TIME ZONE 'UTC' AT TIME ZONE 'E. Europe Standard Time'
WHEN a.Country_TPI__c = 'LT' THEN DATEADD(HOUR,6,s.eventDate) AT TIME ZONE 'UTC' AT TIME ZONE 'E. Europe Standard Time'
WHEN a.Country_TPI__c = 'LV' THEN DATEADD(HOUR,6,s.eventDate) AT TIME ZONE 'UTC' AT TIME ZONE 'E. Europe Standard Time'
WHEN a.Country_TPI__c = 'RO' THEN DATEADD(HOUR,6,s.eventDate) AT TIME ZONE 'UTC' AT TIME ZONE 'E. Europe Standard Time'
WHEN a.Country_TPI__c = 'CZ' THEN DATEADD(HOUR,6,s.eventDate) AT TIME ZONE 'UTC' AT TIME ZONE 'E. Europe Standard Time'
WHEN a.Country_TPI__c = 'SK' THEN DATEADD(HOUR,6,s.eventDate) AT TIME ZONE 'UTC' AT TIME ZONE 'E. Europe Standard Time'
WHEN a.Country_TPI__c = 'AT' THEN DATEADD(HOUR,6,s.eventDate) AT TIME ZONE 'UTC' AT TIME ZONE 'E. Europe Standard Time'
WHEN a.Country_TPI__c = 'GB' THEN DATEADD(HOUR,6,s.eventDate) AT TIME ZONE 'UTC' AT TIME ZONE 'UTC'
WHEN a.Country_TPI__c = 'IE' THEN DATEADD(HOUR,6,s.eventDate) AT TIME ZONE 'UTC' AT TIME ZONE 'UTC'
WHEN a.Country_TPI__c = 'PT' THEN DATEADD(HOUR,6,s.eventDate) AT TIME ZONE 'UTC' AT TIME ZONE 'W. Europe Standard Time'
WHEN a.Country_TPI__c = 'IL' THEN DATEADD(HOUR,6,s.eventDate) AT TIME ZONE 'UTC' AT TIME ZONE 'Israel Standard Time'
WHEN a.Country_TPI__c = 'FI' THEN DATEADD(HOUR,6,s.eventDate) AT TIME ZONE 'UTC' AT TIME ZONE 'Middle East Standard Time'
WHEN a.Country_TPI__c = 'CA' THEN (s.eventDate)
ELSE NULL
END AS sentDate,
jo.Journeyname AS campaignName,
ca.id AS campaignId,
b.bouncesubcategory AS bounceSubcategory,
a.Country_TPI__c As Country,
j.emailId AS EmailId,
IIF(cl.eventDate is null, 0, 1) AS ifClicked,
CASE
WHEN a.Country_TPI__c = 'BG' THEN DATEADD(HOUR,6,cl.eventDate) AT TIME ZONE 'UTC' AT TIME ZONE 'E. Europe Standard Time'
WHEN a.Country_TPI__c = 'EE' THEN DATEADD(HOUR,6,cl.eventDate) AT TIME ZONE 'UTC' AT TIME ZONE 'E. Europe Standard Time'
WHEN a.Country_TPI__c = 'GR' THEN DATEADD(HOUR,6,cl.eventDate) AT TIME ZONE 'UTC' AT TIME ZONE 'E. Europe Standard Time'
WHEN a.Country_TPI__c = 'LT' THEN DATEADD(HOUR,6,cl.eventDate) AT TIME ZONE 'UTC' AT TIME ZONE 'E. Europe Standard Time'
WHEN a.Country_TPI__c = 'LV' THEN DATEADD(HOUR,6,cl.eventDate) AT TIME ZONE 'UTC' AT TIME ZONE 'E. Europe Standard Time'
WHEN a.Country_TPI__c = 'RO' THEN DATEADD(HOUR,6,cl.eventDate) AT TIME ZONE 'UTC' AT TIME ZONE 'E. Europe Standard Time'
WHEN a.Country_TPI__c = 'CZ' THEN DATEADD(HOUR,6,cl.eventDate) AT TIME ZONE 'UTC' AT TIME ZONE 'E. Europe Standard Time'
WHEN a.Country_TPI__c = 'SK' THEN DATEADD(HOUR,6,cl.eventDate) AT TIME ZONE 'UTC' AT TIME ZONE 'E. Europe Standard Time'
WHEN a.Country_TPI__c = 'AT' THEN DATEADD(HOUR,6,cl.eventDate) AT TIME ZONE 'UTC' AT TIME ZONE 'E. Europe Standard Time'
WHEN a.Country_TPI__c = 'GB' THEN DATEADD(HOUR,6,cl.eventDate) AT TIME ZONE 'UTC' AT TIME ZONE 'UTC'
WHEN a.Country_TPI__c = 'IE' THEN DATEADD(HOUR,6,cl.eventDate) AT TIME ZONE 'UTC' AT TIME ZONE 'UTC'
WHEN a.Country_TPI__c = 'PT' THEN DATEADD(HOUR,6,cl.eventDate) AT TIME ZONE 'UTC' AT TIME ZONE 'W. Europe Standard Time'
WHEN a.Country_TPI__c = 'IL' THEN DATEADD(HOUR,6,cl.eventDate) AT TIME ZONE 'UTC' AT TIME ZONE 'Israel Standard Time'
WHEN a.Country_TPI__c = 'FI' THEN DATEADD(HOUR,6,cl.eventDate) AT TIME ZONE 'UTC' AT TIME ZONE 'Middle East Standard Time'
WHEN a.Country_TPI__c = 'CA' THEN (cl.eventDate)
ELSE NULL
END AS lastClickDate,
promo.VaultDocId as vaultDocId,
GETUTCDATE() AS lastModifiedDate
from ENT.[EUCAN_Sent] s
JOIN
ENT.[Account_Salesforce_2] a
ON s.HCP_accountid = a.id
JOIN ENT.[EUCAN_Job] j
on j.jobId = s.jobId
AND j.TriggererSendDefinitionObjectID = s.TriggererSendDefinitionObjectID
LEFT JOIN
(
SELECT eventDate, subscriberID, jobId, TriggererSendDefinitionObjectID,bouncesubcategory,
ROW_NUMBER() OVER (PARTITION BY subscriberID, jobId,TriggererSendDefinitionObjectID ORDER BY eventDate DESC) as rowNum
FROM ENT.[EUCAN_Bounce]
) b
ON b.JobID = s.JobID AND b.rowNum = 1
AND b.TriggererSendDefinitionObjectID = s.TriggererSendDefinitionObjectID
AND b.SubscriberID = s.SubscriberID
LEFT JOIN ENT.[EUCAN_JourneyActivity] ja
ON ja.journeyActivityObjectID = j.TriggererSendDefinitionObjectID
LEFT JOIN ENT.[EUCAN_Journey] jo
ON ja.versionId = jo.versionId
LEFT JOIN
(
select id, name, ROW_NUMBER() OVER (PARTITION BY name ORDER BY createdDate DESC) as rowNum
from ENT.[Campaign_vod__c_Salesforce_1]
) ca on ca.Name = jo.Journeyname and ca.rowNum = 1
LEFT JOIN
(
SELECT VaultDocId, emailId, ROW_NUMBER() OVER (PARTITION BY emailId ORDER BY createdDate DESC) as rowNum
from ENT.[EUCAN_SFMC_Promomat_Mapping_Master_DE]
) promo on promo.emailId = j.emailId and promo.rowNum = 1
LEFT JOIN
(
SELECT eventDate, subscriberID, jobId, TriggererSendDefinitionObjectID,
ROW_NUMBER() OVER (PARTITION BY subscriberID, jobId,TriggererSendDefinitionObjectID ORDER BY eventDate DESC) as rowNum
FROM ENT.[EUCAN_Click]
) cl
ON cl.JobID = s.JobID AND cl.rowNum = 1
AND cl.TriggererSendDefinitionObjectID = s.TriggererSendDefinitionObjectID
AND cl.SubscriberID = s.SubscriberID
WHERE
s.eventDate >= DATEADD(YEAR, -3, GETDATE())
AND a.Country_TPI__c IN ('BG', 'EE', 'GR', 'LT', 'LV', 'RO', 'GB', 'IE', 'PT', 'IL', 'FI', 'CA','CZ','SK','AT')
SQL Name: Dummy
CustomerKey: E57C20E5-9D2B-4B5F-9D13-9775E1511606
TargetDE: PROD_NBA_Unified_Email_Copy
UpdateType: Update
QueryText: SELECT
s.id AS activityId,
'VAE' AS emailType,
s.Account_vod__c AS accountId,
s.OwnerId AS ownerId,
d.name AS templateName,
trim(s.Subject_vod__c) AS subjectLine,
s.Account_Email_vod__c AS toEmailAddress,
s.Status_vod__c AS status,
CASE
WHEN a.Country_TPI__c = 'BG' THEN DATEADD(HOUR,6,s.Email_Sent_Date_vod__c) AT TIME ZONE 'UTC' AT TIME ZONE 'E. Europe Standard Time'
WHEN a.Country_TPI__c = 'EE' THEN DATEADD(HOUR,6,s.Email_Sent_Date_vod__c) AT TIME ZONE 'UTC' AT TIME ZONE 'E. Europe Standard Time'
WHEN a.Country_TPI__c = 'GR' THEN DATEADD(HOUR,6,s.Email_Sent_Date_vod__c) AT TIME ZONE 'UTC' AT TIME ZONE 'E. Europe Standard Time'
WHEN a.Country_TPI__c = 'LT' THEN DATEADD(HOUR,6,s.Email_Sent_Date_vod__c) AT TIME ZONE 'UTC' AT TIME ZONE 'E. Europe Standard Time'
WHEN a.Country_TPI__c = 'LV' THEN DATEADD(HOUR,6,s.Email_Sent_Date_vod__c) AT TIME ZONE 'UTC' AT TIME ZONE 'E. Europe Standard Time'
WHEN a.Country_TPI__c = 'RO' THEN DATEADD(HOUR,6,s.Email_Sent_Date_vod__c) AT TIME ZONE 'UTC' AT TIME ZONE 'E. Europe Standard Time'
WHEN a.Country_TPI__c = 'GB' THEN DATEADD(HOUR,6,s.Email_Sent_Date_vod__c) AT TIME ZONE 'UTC' AT TIME ZONE 'UTC'
WHEN a.Country_TPI__c = 'IE' THEN DATEADD(HOUR,6,s.Email_Sent_Date_vod__c) AT TIME ZONE 'UTC' AT TIME ZONE 'UTC'
WHEN a.Country_TPI__c = 'PT' THEN DATEADD(HOUR,6,s.Email_Sent_Date_vod__c) AT TIME ZONE 'UTC' AT TIME ZONE 'W. Europe Standard Time'
WHEN a.Country_TPI__c = 'IL' THEN DATEADD(HOUR,6,s.Email_Sent_Date_vod__c) AT TIME ZONE 'UTC' AT TIME ZONE 'Israel Standard Time'
WHEN a.Country_TPI__c = 'FI' THEN DATEADD(HOUR,6,s.Email_Sent_Date_vod__c) AT TIME ZONE 'UTC' AT TIME ZONE 'Middle East Standard Time'
WHEN a.Country_TPI__c = 'CA' THEN DATEADD(HOUR,6,s.Email_Sent_Date_vod__c) AT TIME ZONE 'UTC' AT TIME ZONE 'Central European Standard Time'
ELSE NULL
END AS sentDate,
NULL AS campaignName,
NULL AS campaignId,
NULL AS bounceSubcategory,
a.Country_TPI__c As Country,
d.Vault_Document_ID_vod__c AS EmailId,
IIF(s.Last_Click_Date_TPI__c IS NULL, 0, 1) AS ifClicked,
CASE
WHEN a.Country_TPI__c = 'BG' THEN DATEADD(HOUR,6,s.Last_Click_Date_TPI__c) AT TIME ZONE 'UTC' AT TIME ZONE 'E. Europe Standard Time'
WHEN a.Country_TPI__c = 'EE' THEN DATEADD(HOUR,6,s.Last_Click_Date_TPI__c) AT TIME ZONE 'UTC' AT TIME ZONE 'E. Europe Standard Time'
WHEN a.Country_TPI__c = 'GR' THEN DATEADD(HOUR,6,s.Last_Click_Date_TPI__c) AT TIME ZONE 'UTC' AT TIME ZONE 'E. Europe Standard Time'
WHEN a.Country_TPI__c = 'LT' THEN DATEADD(HOUR,6,s.Last_Click_Date_TPI__c) AT TIME ZONE 'UTC' AT TIME ZONE 'E. Europe Standard Time'
WHEN a.Country_TPI__c = 'LV' THEN DATEADD(HOUR,6,s.Last_Click_Date_TPI__c) AT TIME ZONE 'UTC' AT TIME ZONE 'E. Europe Standard Time'
WHEN a.Country_TPI__c = 'RO' THEN DATEADD(HOUR,6,s.Last_Click_Date_TPI__c) AT TIME ZONE 'UTC' AT TIME ZONE 'E. Europe Standard Time'
WHEN a.Country_TPI__c = 'GB' THEN DATEADD(HOUR,6,s.Last_Click_Date_TPI__c) AT TIME ZONE 'UTC' AT TIME ZONE 'UTC'
WHEN a.Country_TPI__c = 'IE' THEN DATEADD(HOUR,6,s.Last_Click_Date_TPI__c) AT TIME ZONE 'UTC' AT TIME ZONE 'UTC'
WHEN a.Country_TPI__c = 'PT' THEN DATEADD(HOUR,6,s.Last_Click_Date_TPI__c) AT TIME ZONE 'UTC' AT TIME ZONE 'W. Europe Standard Time'
WHEN a.Country_TPI__c = 'IL' THEN DATEADD(HOUR,6,s.Last_Click_Date_TPI__c) AT TIME ZONE 'UTC' AT TIME ZONE 'Israel Standard Time'
WHEN a.Country_TPI__c = 'FI' THEN DATEADD(HOUR,6,s.Last_Click_Date_TPI__c) AT TIME ZONE 'UTC' AT TIME ZONE 'Middle East Standard Time'
WHEN a.Country_TPI__c = 'CA' THEN DATEADD(HOUR,6,s.Last_Click_Date_TPI__c) AT TIME ZONE 'UTC' AT TIME ZONE 'Central European Standard Time'
ELSE NULL
END AS lastClickDate,
d.Vault_Document_ID_vod__c AS vaultDocId,
GETUTCDATE() AS lastModifiedDate
FROM
ENT.[Sent_Email_vod__c_Salesforce] s
LEFT JOIN
ENT.[Approved_Document_vod__c_Salesforce] d
ON s.Approved_Email_Template_vod__c = d.id
LEFT JOIN
ENT.[Account_Salesforce_2] a
ON s.Account_vod__c = a.id
WHERE
s.Email_Sent_Date_vod__c >= DATEADD(YEAR, -3, GETDATE())
AND a.Country_TPI__c IN ('BG', 'EE', 'GR', 'LT', 'LV', 'RO', 'GB', 'IE', 'PT', 'IL', 'FI', 'CA')
UNION ALL
SELECT DISTINCT
s.ActivityId AS activityId,
'SFMC' AS emailType,
s.HCP_accountid AS accountId,
NULL AS ownerId,
j.emailName AS templateName,
trim(j.EmailSubject) AS subjectLine,
a.PersonEmail AS toEmailAddress,
'Email_Sent' AS status,
CASE
WHEN a.Country_TPI__c = 'BG' THEN DATEADD(HOUR,6,s.eventDate) AT TIME ZONE 'UTC' AT TIME ZONE 'E. Europe Standard Time'
WHEN a.Country_TPI__c = 'EE' THEN DATEADD(HOUR,6,s.eventDate) AT TIME ZONE 'UTC' AT TIME ZONE 'E. Europe Standard Time'
WHEN a.Country_TPI__c = 'GR' THEN DATEADD(HOUR,6,s.eventDate) AT TIME ZONE 'UTC' AT TIME ZONE 'E. Europe Standard Time'
WHEN a.Country_TPI__c = 'LT' THEN DATEADD(HOUR,6,s.eventDate) AT TIME ZONE 'UTC' AT TIME ZONE 'E. Europe Standard Time'
WHEN a.Country_TPI__c = 'LV' THEN DATEADD(HOUR,6,s.eventDate) AT TIME ZONE 'UTC' AT TIME ZONE 'E. Europe Standard Time'
WHEN a.Country_TPI__c = 'RO' THEN DATEADD(HOUR,6,s.eventDate) AT TIME ZONE 'UTC' AT TIME ZONE 'E. Europe Standard Time'
WHEN a.Country_TPI__c = 'GB' THEN DATEADD(HOUR,6,s.eventDate) AT TIME ZONE 'UTC' AT TIME ZONE 'UTC'
WHEN a.Country_TPI__c = 'IE' THEN DATEADD(HOUR,6,s.eventDate) AT TIME ZONE 'UTC' AT TIME ZONE 'UTC'
WHEN a.Country_TPI__c = 'PT' THEN DATEADD(HOUR,6,s.eventDate) AT TIME ZONE 'UTC' AT TIME ZONE 'W. Europe Standard Time'
WHEN a.Country_TPI__c = 'IL' THEN DATEADD(HOUR,6,s.eventDate) AT TIME ZONE 'UTC' AT TIME ZONE 'Israel Standard Time'
WHEN a.Country_TPI__c = 'FI' THEN DATEADD(HOUR,6,s.eventDate) AT TIME ZONE 'UTC' AT TIME ZONE 'Middle East Standard Time'
WHEN a.Country_TPI__c = 'CA' THEN DATEADD(HOUR,6,s.eventDate) AT TIME ZONE 'UTC' AT TIME ZONE 'Central European Standard Time'
ELSE NULL
END AS sentDate,
jo.Journeyname AS campaignName,
ca.id AS campaignId,
b.bouncesubcategory AS bounceSubcategory,
a.Country_TPI__c As Country,
j.emailId AS EmailId,
IIF(cl.eventDate is null, 0, 1) AS ifClicked,
CASE
WHEN a.Country_TPI__c = 'BG' THEN DATEADD(HOUR,6,cl.eventDate) AT TIME ZONE 'UTC' AT TIME ZONE 'E. Europe Standard Time'
WHEN a.Country_TPI__c = 'EE' THEN DATEADD(HOUR,6,cl.eventDate) AT TIME ZONE 'UTC' AT TIME ZONE 'E. Europe Standard Time'
WHEN a.Country_TPI__c = 'GR' THEN DATEADD(HOUR,6,cl.eventDate) AT TIME ZONE 'UTC' AT TIME ZONE 'E. Europe Standard Time'
WHEN a.Country_TPI__c = 'LT' THEN DATEADD(HOUR,6,cl.eventDate) AT TIME ZONE 'UTC' AT TIME ZONE 'E. Europe Standard Time'
WHEN a.Country_TPI__c = 'LV' THEN DATEADD(HOUR,6,cl.eventDate) AT TIME ZONE 'UTC' AT TIME ZONE 'E. Europe Standard Time'
WHEN a.Country_TPI__c = 'RO' THEN DATEADD(HOUR,6,cl.eventDate) AT TIME ZONE 'UTC' AT TIME ZONE 'E. Europe Standard Time'
WHEN a.Country_TPI__c = 'GB' THEN DATEADD(HOUR,6,cl.eventDate) AT TIME ZONE 'UTC' AT TIME ZONE 'UTC'
WHEN a.Country_TPI__c = 'IE' THEN DATEADD(HOUR,6,cl.eventDate) AT TIME ZONE 'UTC' AT TIME ZONE 'UTC'
WHEN a.Country_TPI__c = 'PT' THEN DATEADD(HOUR,6,cl.eventDate) AT TIME ZONE 'UTC' AT TIME ZONE 'W. Europe Standard Time'
WHEN a.Country_TPI__c = 'IL' THEN DATEADD(HOUR,6,cl.eventDate) AT TIME ZONE 'UTC' AT TIME ZONE 'Israel Standard Time'
WHEN a.Country_TPI__c = 'FI' THEN DATEADD(HOUR,6,cl.eventDate) AT TIME ZONE 'UTC' AT TIME ZONE 'Middle East Standard Time'
WHEN a.Country_TPI__c = 'CA' THEN DATEADD(HOUR,6,cl.eventDate) AT TIME ZONE 'UTC' AT TIME ZONE 'Central European Standard Time'
ELSE NULL
END AS lastClickDate,
promo.VaultDocId as vaultDocId,
GETUTCDATE() AS lastModifiedDate
from ENT.[EUCAN_Sent] s
JOIN
ENT.[Account_Salesforce_2] a
ON s.HCP_accountid = a.id
JOIN ENT.[EUCAN_Job] j
on j.jobId = s.jobId
AND j.TriggererSendDefinitionObjectID = s.TriggererSendDefinitionObjectID
LEFT JOIN
(
SELECT eventDate, subscriberID, jobId, TriggererSendDefinitionObjectID,bouncesubcategory,
ROW_NUMBER() OVER (PARTITION BY subscriberID, jobId,TriggererSendDefinitionObjectID ORDER BY eventDate DESC) as rowNum
FROM ENT.[EUCAN_Bounce]
) b
ON b.JobID = s.JobID AND b.rowNum = 1
AND b.TriggererSendDefinitionObjectID = s.TriggererSendDefinitionObjectID
AND b.SubscriberID = s.SubscriberID
LEFT JOIN ENT.[EUCAN_JourneyActivity] ja
ON ja.journeyActivityObjectID = j.TriggererSendDefinitionObjectID
LEFT JOIN ENT.[EUCAN_Journey] jo
ON ja.versionId = jo.versionId
LEFT JOIN
(
select id, name, ROW_NUMBER() OVER (PARTITION BY name ORDER BY createdDate DESC) as rowNum
from ENT.[Campaign_vod__c_Salesforce_1]
) ca on ca.Name = jo.Journeyname and ca.rowNum = 1
LEFT JOIN
(
SELECT VaultDocId, emailId, ROW_NUMBER() OVER (PARTITION BY emailId ORDER BY createdDate DESC) as rowNum
from ENT.[EUCAN_SFMC_Promomat_Mapping_Master_DE]
) promo on promo.emailId = j.emailId and promo.rowNum = 1
LEFT JOIN
(
SELECT eventDate, subscriberID, jobId, TriggererSendDefinitionObjectID,
ROW_NUMBER() OVER (PARTITION BY subscriberID, jobId,TriggererSendDefinitionObjectID ORDER BY eventDate DESC) as rowNum
FROM ENT.[EUCAN_Click]
) cl
ON cl.JobID = s.JobID AND cl.rowNum = 1
AND cl.TriggererSendDefinitionObjectID = s.TriggererSendDefinitionObjectID
AND cl.SubscriberID = s.SubscriberID
WHERE
s.eventDate >= DATEADD(YEAR, -3, GETDATE())
AND a.Country_TPI__c IN ('BG', 'EE', 'GR', 'LT', 'LV', 'RO', 'GB', 'IE', 'PT', 'IL', 'FI', 'CA')
SQL Name: PROD_NBA_Unified_Email
CustomerKey: 44272478-57D6-4EC8-B2F3-F9B1ED448482
TargetDE: PROD_NBA_Unified_Email
UpdateType: Update
QueryText: SELECT
s.id AS activityId,
'VAE' AS emailType,
s.Account_vod__c AS accountId,
s.OwnerId AS ownerId,
d.name AS templateName,
trim(s.Subject_vod__c) AS subjectLine,
s.Account_Email_vod__c AS toEmailAddress,
s.Status_vod__c AS status,
DATEADD(HOUR, 6, s.Email_Sent_Date_vod__c) AT TIME ZONE 'UTC' AT TIME ZONE 'Central European Standard Time' AS sentDate,
NULL AS campaignName,
NULL AS campaignId,
NULL AS bounceSubcategory,
a.Country_TPI__c As Country,
d.Vault_Document_ID_vod__c AS EmailId,
IIF(s.Last_Click_Date_TPI__c IS NULL, 0, 1) AS ifClicked,
DATEADD(HOUR, 6, s.Last_Click_Date_TPI__c) AT TIME ZONE 'UTC' AT TIME ZONE 'Central European Standard Time' as lastClickDate,
d.Vault_Document_ID_vod__c AS vaultDocId,
GETUTCDATE() AS lastModifiedDate
FROM
ENT.[Sent_Email_vod__c_Salesforce] s
LEFT JOIN
ENT.[Approved_Document_vod__c_Salesforce] d
ON s.Approved_Email_Template_vod__c = d.id
LEFT JOIN
ENT.[Account_Salesforce_2] a
ON s.Account_vod__c = a.id
WHERE
s.Email_Sent_Date_vod__c >= DATEADD(YEAR, -3, GETDATE())
AND a.Country_TPI__c ='NL'
UNION ALL
SELECT DISTINCT
s.ActivityId AS activityId,
'SFMC' AS emailType,
s.HCP_accountid AS accountId,
NULL AS ownerId,
j.emailName AS templateName,
trim(j.EmailSubject) AS subjectLine,
a.PersonEmail AS toEmailAddress,
'Email_Sent' AS status,
DATEADD(HOUR, 6, s.eventDate) AT TIME ZONE 'UTC' AT TIME ZONE 'Central European Standard Time' AS sentDate,
jo.Journeyname AS campaignName,
ca.id AS campaignId,
b.bouncesubcategory AS bounceSubcategory,
a.Country_TPI__c As Country,
j.emailId AS EmailId,
IIF(cl.eventDate is null, 0, 1) AS ifClicked,
DATEADD(HOUR, 6, cl.eventDate) AT TIME ZONE 'UTC' AT TIME ZONE 'Central European Standard Time' AS lastClickDate,
promo.VaultDocId as vaultDocId,
GETUTCDATE() AS lastModifiedDate
from ENT.[EUCAN_Sent] s
JOIN
ENT.[Account_Salesforce_2] a
ON s.HCP_accountid = a.id
JOIN ENT.[EUCAN_Job] j
on j.jobId = s.jobId
AND j.TriggererSendDefinitionObjectID = s.TriggererSendDefinitionObjectID
LEFT JOIN
(
SELECT eventDate, subscriberID, jobId, TriggererSendDefinitionObjectID,bouncesubcategory,
ROW_NUMBER() OVER (PARTITION BY subscriberID, jobId,TriggererSendDefinitionObjectID ORDER BY eventDate DESC) as rowNum
FROM ENT.[EUCAN_Bounce]
) b
ON b.JobID = s.JobID AND b.rowNum = 1
AND b.TriggererSendDefinitionObjectID = s.TriggererSendDefinitionObjectID
AND b.SubscriberID = s.SubscriberID
LEFT JOIN ENT.[EUCAN_JourneyActivity] ja
ON ja.journeyActivityObjectID = j.TriggererSendDefinitionObjectID
LEFT JOIN ENT.[EUCAN_Journey] jo
ON ja.versionId = jo.versionId
LEFT JOIN
(
select id, name, ROW_NUMBER() OVER (PARTITION BY name ORDER BY createdDate DESC) as rowNum
from ENT.[Campaign_vod__c_Salesforce_1]
) ca on ca.Name = jo.Journeyname and ca.rowNum = 1
LEFT JOIN
(
SELECT VaultDocId, emailId, ROW_NUMBER() OVER (PARTITION BY emailId ORDER BY createdDate DESC) as rowNum
from ENT.[EUCAN_SFMC_Promomat_Mapping_Master_DE]
) promo on promo.emailId = j.emailId and promo.rowNum = 1
LEFT JOIN
(
SELECT eventDate, subscriberID, jobId, TriggererSendDefinitionObjectID,
ROW_NUMBER() OVER (PARTITION BY subscriberID, jobId,TriggererSendDefinitionObjectID ORDER BY eventDate DESC) as rowNum
FROM ENT.[EUCAN_Click]
) cl
ON cl.JobID = s.JobID AND cl.rowNum = 1
AND cl.TriggererSendDefinitionObjectID = s.TriggererSendDefinitionObjectID
AND cl.SubscriberID = s.SubscriberID
WHERE
s.eventDate >= DATEADD(YEAR, -3, GETDATE())
AND a.Country_TPI__c ='NL'
SQL Name: PROD_Unified_Email_1st
CustomerKey: 8B6ED361-35D8-4CCA-A1C4-F4F3783FF1C5
TargetDE: PROD_NBA_Unified_Email_1st
UpdateType: Update
QueryText: SELECT
s.id AS activityId,
'VAE' AS emailType,
s.Account_vod__c AS accountId,
s.OwnerId AS ownerId,
d.name AS templateName,
TRIM(s.Subject_vod__c) AS subjectLine,
s.Account_Email_vod__c AS toEmailAddress,
s.Status_vod__c AS status,
CASE
WHEN a.Country_TPI__c IN ('BG','EE','GR','LT','LV','RO','IL','FI') THEN DATEADD(HOUR, 3, s.Email_Sent_Date_vod__c)
WHEN a.Country_TPI__c IN ('GB','IE','PT') THEN DATEADD(HOUR, 1, s.Email_Sent_Date_vod__c)
WHEN a.Country_TPI__c = 'CA' THEN DATEADD(HOUR, 6, s.Email_Sent_Date_vod__c)
ELSE s.Email_Sent_Date_vod__c
END AS sentDate,
NULL AS campaignName,
NULL AS campaignId,
NULL AS bounceSubcategory,
d.Vault_Document_ID_vod__c AS EmailId,
a.Country_TPI__c AS Country,
NULL AS keyMessages,
IIF(s.Last_Click_Date_TPI__c IS NULL, 0, 1) AS IfClicked,
CASE
WHEN a.Country_TPI__c IN ('BG','EE','GR','LT','LV','RO','IL','FI') THEN DATEADD(HOUR, 3, s.Last_Click_Date_TPI__c)
WHEN a.Country_TPI__c IN ('GB','IE','PT') THEN DATEADD(HOUR, 1, s.Last_Click_Date_TPI__c)
WHEN a.Country_TPI__c = 'CA' THEN DATEADD(HOUR, 6, s.Last_Click_Date_TPI__c)
ELSE s.Last_Click_Date_TPI__c
END AS lastClickDate,
d.Vault_Document_ID_vod__c AS vaultDocId,
GETUTCDATE() AS lastModifiedDate
FROM
ENT.[Sent_Email_vod__c_Salesforce] s
LEFT JOIN
ENT.[Approved_Document_vod__c_Salesforce] d
ON s.Approved_Email_Template_vod__c = d.id
LEFT JOIN
ENT.[Account_Salesforce_2] a
ON s.Account_vod__c = a.id
WHERE
s.Email_Sent_Date_vod__c >= DATEADD(YEAR, -3, GETDATE())
AND a.Country_TPI__c IN ('BG', 'EE', 'GR', 'LT', 'LV', 'RO', 'GB', 'IE', 'PT', 'IL', 'FI', 'CA')
UNION ALL
SELECT DISTINCT
s.ActivityId AS activityId,
'SFMC' AS emailType,
s.HCP_accountid AS accountId,
NULL AS ownerId,
j.emailName AS templateName,
TRIM(j.EmailSubject) AS subjectLine,
a.PersonEmail AS toEmailAddress,
'Email_Sent' AS status,
CASE
WHEN a.Country_TPI__c IN ('BG','EE','GR','LT','LV','RO','IL','FI') THEN DATEADD(HOUR, 3, s.eventDate)
WHEN a.Country_TPI__c IN ('GB','IE','PT') THEN DATEADD(HOUR, 1, s.eventDate)
WHEN a.Country_TPI__c = 'CA' THEN DATEADD(HOUR, 6, s.eventDate)
ELSE s.eventDate
END AS sentDate,
jo.Journeyname AS campaignName,
ca.id AS campaignId,
b.bouncesubcategory AS bounceSubcategory,
j.emailId AS EmailId,
a.Country_TPI__c AS Country,
NULL AS keyMessages,
IIF(cl.eventDate IS NULL, 0, 1) AS IfClicked,
CASE
WHEN a.Country_TPI__c IN ('BG','EE','GR','LT','LV','RO','IL','FI') THEN DATEADD(HOUR, 3, cl.eventDate)
WHEN a.Country_TPI__c IN ('GB','IE','PT') THEN DATEADD(HOUR, 1, cl.eventDate)
WHEN a.Country_TPI__c = 'CA' THEN DATEADD(HOUR, 6, cl.eventDate)
ELSE cl.eventDate
END AS lastClickDate,
promo.VaultDocId AS vaultDocId,
GETUTCDATE() AS lastModifiedDate
FROM ENT.[EUCAN_Sent] s
JOIN ENT.[Account_Salesforce_2] a
ON s.HCP_accountid = a.id
JOIN ENT.[EUCAN_Job] j
ON j.jobId = s.jobId
AND j.TriggererSendDefinitionObjectID = s.TriggererSendDefinitionObjectID
LEFT JOIN (
SELECT eventDate, subscriberID, jobId, TriggererSendDefinitionObjectID, bouncesubcategory,
ROW_NUMBER() OVER (PARTITION BY subscriberID, jobId, TriggererSendDefinitionObjectID ORDER BY eventDate DESC) AS rowNum
FROM ENT.[EUCAN_Bounce]
) b ON b.JobID = s.JobID AND b.rowNum = 1
AND b.TriggererSendDefinitionObjectID = s.TriggererSendDefinitionObjectID
AND b.SubscriberID = s.SubscriberID
LEFT JOIN ENT.[EUCAN_JourneyActivity] ja
ON ja.journeyActivityObjectID = j.TriggererSendDefinitionObjectID
LEFT JOIN ENT.[EUCAN_Journey] jo
ON ja.versionId = jo.versionId
LEFT JOIN (
SELECT id, name, ROW_NUMBER() OVER (PARTITION BY name ORDER BY createdDate DESC) AS rowNum
FROM ENT.[Campaign_vod__c_Salesforce_1]
) ca ON ca.Name = jo.Journeyname AND ca.rowNum = 1
LEFT JOIN (
SELECT VaultDocId, emailId, ROW_NUMBER() OVER (PARTITION BY emailId ORDER BY createdDate DESC) AS rowNum
FROM ENT.[EUCAN_SFMC_Promomat_Mapping_Master_DE]
) promo ON promo.emailId = j.emailId AND promo.rowNum = 1
LEFT JOIN (
SELECT eventDate, subscriberID, jobId, TriggererSendDefinitionObjectID,
ROW_NUMBER() OVER (PARTITION BY subscriberID, jobId, TriggererSendDefinitionObjectID ORDER BY eventDate DESC) AS rowNum
FROM ENT.[EUCAN_Click]
) cl ON cl.JobID = s.JobID AND cl.rowNum = 1
AND cl.TriggererSendDefinitionObjectID = s.TriggererSendDefinitionObjectID
AND cl.SubscriberID = s.SubscriberID
WHERE
s.eventDate >= DATEADD(YEAR, -3, GETDATE())
AND a.Country_TPI__c IN ('BG', 'EE', 'GR', 'LT', 'LV', 'RO', 'GB', 'IE', 'PT', 'IL', 'FI', 'CA')
Queries affected- 4