Delete Duplicate Tasks

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.
Edit section


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