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
SQL Name: interactivequery-524001054-710272470
CustomerKey: 075f511c-723d-4019-8e7e-6b64b24a7d42
TargetDE: QueryStudioResults at 2026-08-17T0812430000
UpdateType: Overwrite
QueryText: SELECT
rtrim(left(coalesce(CAST(P.personcontactid as char(255)),''), 255)) as personcontactid, rtrim(left(coalesce(CAST(P.personalizedInvitationId as char(255)),''), 255)) as personalizedInvitationId, rtrim(left(coalesce(CAST(P.ActivityId as char(255)),''), 255)) as ActivityId, rtrim(left(coalesce(CAST(P.country as char(255)),''), 255)) as country, rtrim(left(coalesce(CAST(P.emailaddress as char(255)),''), 255)) as emailaddress, rtrim(left(coalesce(CAST(P.JobID as char(255)),''), 255)) as JobID, rtrim(left(coalesce(CAST(P.ListID as char(255)),''), 255)) as ListID, rtrim(left(coalesce(CAST(P.BatchID as char(255)),''), 255)) as BatchID, rtrim(left(coalesce(CAST(P.SubID as char(255)),''), 255)) as SubID, rtrim(left(coalesce(CAST(P.TriggeredSendID as char(255)),''), 255)) as TriggeredSendID, rtrim(left(coalesce(CAST(P.Insert_Date as char(255)),''), 255)) as Insert_Date, rtrim(left(coalesce(CAST(P.SentDate as char(255)),''), 255)) as SentDate, rtrim(left(coalesce(CAST(P.OpenDate as char(255)),''), 255)) as OpenDate, rtrim(left(coalesce(CAST(P.ClickDate as char(255)),''), 255)) as ClickDate, rtrim(left(coalesce(CAST(P.BounceDate as char(255)),''), 255)) as BounceDate, rtrim(left(coalesce(CAST(P.Status as char(255)),''), 255)) as Status, rtrim(left(coalesce(CAST('False' as char(255)),''), 255)) 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)
Queries affected- 8