SQL Name: TakedaID_get_all_the_Latest_records_with_Status_Updated_is_False
CustomerKey: c3cf61c1-b5d3-4aa8-9726-d6e399f09cdd
TargetDE: TakedaID_Update_PI_Status_to_CRM_Latest_records
UpdateType: Overwrite
QueryText: Select t1.personcontactid,
t1.personalizedInvitationId,
t1.ActivityId,
t1.Insert_Date,
t1.Status,
t1.Is_Status_Updated,
t1.RowNumber
From (
Select personcontactid,
personalizedInvitationId,
ActivityId,
Insert_Date,
Status,
Is_Status_Updated,
ROW_NUMBER() OVER(PARTITION BY personcontactid ORDER BY Insert_Date DESC) AS RowNumber
FROM ENT.TakedaID_Update_PI_Status_to_CRM_duplicate_records
) t1
WHERE t1.RowNumber = 1
SQL Name: TakedaID_get_all_the_records_with_Is_Status_Updated_is_False
CustomerKey: 46274b64-d6f7-4483-a1b4-2f5545bf26a3
TargetDE: TakedaID_Update_PI_Status_to_CRM_duplicate_records
UpdateType: Overwrite
QueryText: Select aa.personcontactid,
aa.personalizedInvitationId,
aa.ActivityId,
aa.Insert_Date,
bb.Instances AS Instances,
aa.Status,
aa.Is_Status_Updated
From ENT.TakedaID_Update_PI_Status_to_CRM aa
INNER JOIN (
Select personcontactid,
COUNT(*) AS Instances
From ENT.TakedaID_Update_PI_Status_to_CRM
Where Is_Status_Updated = 'False'
GROUP BY personcontactid
) bb
ON aa.personcontactid = bb.personcontactid
WHERE bb.Instances > 1 and aa.Is_Status_Updated = 'False'
SQL Name: TakedaID_Update_PI_Status_to_CRM
CustomerKey: 1477c332-b607-4f86-8166-d08630fe258f
TargetDE: TakedaID_Update_PI_Status_to_CRM
UpdateType: Update
QueryText: SELECT
P.personcontactid,
P.personalizedInvitationId,
P.ActivityId,
P.country,
P.emailaddress,
P.JobID,
P.ListID,
P.BatchID,
P.SubID,
P.TriggeredSendID,
P.Insert_Date,
P.SentDate,
P.OpenDate,
P.ClickDate,
P.BounceDate,
P.Status,
'False' as Is_Status_Updated
FROM ENT.TakedaID_Populate_PI_Status_DE P
LEFT JOIN ENT.TakedaID_Update_PI_Status_to_CRM U
ON U.personcontactid = P.personcontactid AND U.JobID = P.JobID AND U.ListID = P.ListID AND U.BatchID = P.BatchID
Where P.Status != U.Status
SQL Name: TakedaID_Update_PI_Status_to_CRM_Old_records_Not_Updated
CustomerKey: b7999a3c-7e73-4c8f-8117-302879f8ccf5
TargetDE: TakedaID_Update_PI_Status_to_CRM_Old_records_Not_Updated
UpdateType: Overwrite
QueryText: Select * FROM ENT.TakedaID_Update_PI_Status_to_CRM_duplicate_records
Where ActivityId NOT IN (Select ActivityId from ENT.TakedaID_Update_PI_Status_to_CRM_Latest_records )
SQL Name: TakedaID_TakedaID_Update_PI_Status_to_CRM_DE_Backup
CustomerKey: c64218b8-0715-439f-aaa7-18c85ee45549
TargetDE: TakedaID_Update_PI_Status_to_CRM_Backup_DE
UpdateType: Overwrite
QueryText: Select * From ENT.TakedaID_Update_PI_Status_to_CRM
SQL Name: TakedaID_Update_PI_Status_to_CRM_New_Reocrds
CustomerKey: 5e90f535-f385-4b5a-ab2e-134cf68bab3a
TargetDE: TakedaID_Update_PI_Status_to_CRM
UpdateType: Append
QueryText: SELECT
P.personcontactid,
P.personalizedInvitationId,
P.ActivityId,
P.country,
P.emailaddress,
P.JobID,
P.ListID,
P.BatchID,
P.SubID,
P.TriggeredSendID,
P.Insert_Date,
P.SentDate,
P.OpenDate,
P.ClickDate,
P.BounceDate,
P.Status,
'False' as Is_Status_Updated
FROM ENT.TakedaID_Populate_PI_Status_DE P
Where P.ActivityId NOT IN (Select ActivityId from ENT.TakedaID_Update_PI_Status_to_CRM)
SQL Name: TakedaID_Mark_duplicate_records_Is_Status_Updated_is_True
CustomerKey: ac3a95ad-5610-45ef-9f73-6a389c7cd185
TargetDE: TakedaID_Update_PI_Status_to_CRM
UpdateType: Update
QueryText: Select
T.ActivityId as ActivityId,
'True' as Is_Status_Updated
From ENT.TakedaID_Update_PI_Status_to_CRM_Old_records_Not_Updated T
Join ENT.TakedaID_Update_PI_Status_to_CRM O
On T.ActivityId = O.ActivityId
Queries affected- 7