Deletes Tasks if there is another, earlier Task on the same calendar day for the same person or opportunity, with the same subject, description, activity type, status, and state. Works only for CRM 4.0 -- 2011 does not use DeletionStateCode. UPDATE t SET DeletionStateCode = 2 FROM Task AS t INNER JOIN Task as te -- Earlier version of same activity ON t.RegardingObjectId = te.RegardingObjectId AND t.a422_rel_activityvalueid = te.a422_rel_activityvalueid AND t.ActivityId <> te.ActivityId LEFT JOIN Opportunity AS o ON (t.RegardingObjectId = o.OpportunityId OR t.RegardingObjectId = o.ContactId) AND o.A422_FirstActivity_GUID = CONVERT(varchar(100), t.ActivityId) WHERE o.OpportunityId Is Null -- Don't delete activity if set as first activity for opportunity AND (t.CreatedOn > te.CreatedOn OR(t.CreatedOn = te.CreatedOn AND t.ActivityId >te.ActivityId)) AND DATEDIFF(DAY, t.CreatedOn, te.CreatedOn) = 0 AND IsNull(t.Description, '') = IsNull(te.Description, '') AND IsNull(t.Subject, '') = IsNull(te.Subject, '') AND t.StateCode = te.StateCode AND t.StatusCode = te.StatusCode |
|