Duplicate opportunity merging en masse

The first requirement for merging opportunities is a ranking of all opportunity statuses for the client so that the most advanced opportunity is retained, so that incomplete applications will not be replaced by inquiries, or denied applications by incomplete ones.  This data should be entered into the Temp_OppStatusRank table created below.  The Temp_DuplicateOpportunities table will hold duplicate opportunities for merging.  Run this first part of the script first.

/****** Object:  Table [dbo].[Temp_OppStatusRank]    Script Date: 02/22/2011 12:21:35 ******/
CREATETABLE [dbo].[Temp_OppStatusRank](
      [Category] [nchar](100)NOTNULL,
      [Value] [nchar](100)NOTNULL,
      [Rank] [int] NOTNULL,
      [CategoryGUID] [uniqueidentifier] NULL,
      [ValueGUID] [uniqueidentifier] NULL
)ON[PRIMARY]
GO
/****** Object:  Table [dbo].[Temp_DuplicateOpportunities]    Script Date: 02/22/2011 12:22:17 ******/
CREATETABLE[dbo].[Temp_DuplicateOpportunities](
      [ContactId] [uniqueidentifier] NOTNULL,
      [DuplicateOppId] [uniqueidentifier] NOTNULL,
      [PreferredOppId] [uniqueidentifier] NULL,
      [Closed] [bit] NOTNULLDEFAULT0
)ON[PRIMARY]
GO

Enter ranking of opp statuses into Temp_OppStatusRank table.

The next section of the script identifies the opportunities to be merged, placing them into the Temp_DuplicateOpportunities table, and then merges subrecords.  Duplicates are defined as two or more opportunities for the same person with the same entry term and opportunity type.

INSERT INTO Temp_DuplicateOpportunities
      (ContactId, PreferredOppId, DuplicateOppId)
SELECT best.ContactId, best.OpportunityId, notbest.OpportunityId
      --, best.a422_rel_oppstatusvalueidName, bestrank.[Rank]
      --, notbest.a422_rel_oppstatusvalueidName, notbestrank.[Rank]
FROM Opportunity AS best
      INNER JOIN Temp_OppStatusRank AS bestrank
            ON best.a422_rel_oppstatusvalueid = bestrank.ValueGUID
      INNER JOIN Opportunity AS notbest
            ON best.ContactId = notbest.ContactId
            AND best.a422_rel_entrytermid = notbest.a422_rel_entrytermid
            AND best.a422_rel_opptypeid = notbest.a422_rel_opptypeid
            AND best.OpportunityId <> notbest.OpportunityId
            AND best.StateCode = 0 AND best.StatusCode = 1
            AND notbest.StateCode =0 AND notbest.StatusCode = 1
      INNER JOIN Temp_OppStatusRank AS notbestrank
            ON notbest.a422_rel_oppstatusvalueid = notbestrank.ValueGUID
      LEFT JOIN (Opportunity AS evenbetter
            INNER JOIN Temp_OppStatusRank AS evenbetterrank
                  ON evenbetter.a422_rel_oppstatusvalueid = evenbetterrank.ValueGUID)
            ON evenbetter.ContactId = best.ContactId
                  AND evenbetter.OpportunityId <> best.OpportunityId
                  AND evenbetter.a422_rel_entrytermid = best.a422_rel_entrytermid
                  AND evenbetter.a422_rel_opptypeid = best.a422_rel_opptypeid
                  AND evenbetter.StateCode =0 AND evenbetter.StatusCode =1
                  AND evenbetter.OpportunityId <> best.OpportunityId
                  AND (bestrank.[Rank] < evenbetterrank.[Rank]
                        OR (bestrank.[Rank] = evenbetterrank.[Rank] AND evenbetter.CreatedOn > best.CreatedOn)
                        OR (bestrank.[Rank] = evenbetterrank.[Rank] AND evenbetter.CreatedOn = best.CreatedOn
AND evenbetter.OpportunityId > best.OpportunityId))
WHERE evenbetter.OpportunityId Is Null
      AND (notbestrank.[Rank] < bestrank.[Rank]
            OR (notbestrank.[Rank] = bestrank.[Rank] AND best.CreatedOn > notbest.CreatedOn)
            OR (notbestrank.[Rank] = bestrank.[Rank] AND best.CreatedOn = notbest.CreatedOn
AND best.OpportunityId > notbest.OpportunityId)) 

GO

UPDATE t
SET PartyId = o.OpportunityId, PartyIdName = o.Name
FROM ActivityPartyBase AS t
      INNER JOIN Temp_DuplicateOpportunities AS dups
            ON t.PartyId = dups.DuplicateOppId
      INNER JOIN Opportunity AS o
            ON dups.PreferredOppId = o.OpportunityId
WHERE dups.Closed = 0

UPDATE t
SET RegardingObjectId = o.OpportunityId, RegardingObjectIdName = o.Name
FROM ActivityPointerBase AS t
      INNER JOIN Temp_DuplicateOpportunities AS dups
            ON t.RegardingObjectId = dups.DuplicateOppId
      INNER JOIN Opportunity AS o
            ON dups.PreferredOppId = o.OpportunityId
WHERE dups.Closed = 0

UPDATE t
SET a422_opportunityid = o.OpportunityId, a422_name = o.Name
FROM A422_OpportunityStatusChangeExtensionBase AS t
      INNER JOIN Temp_DuplicateOpportunities AS dups
            ON t.a422_opportunityid = dups.DuplicateOppId
      INNER JOIN Opportunity AS o
            ON dups.PreferredOppId = o.OpportunityId
WHERE dups.Closed = 0

UPDATE t
SET a422_rel_opportunityid = o.OpportunityId, a422_name = o.Name
FROM A422_InterestExtensionBase AS t
      INNER JOIN Temp_DuplicateOpportunities AS dups
            ON t.a422_rel_opportunityid = dups.DuplicateOppId
      INNER JOIN Opportunity AS o
            ON dups.PreferredOppId = o.OpportunityId
WHERE dups.Closed = 0

UPDATE A422_AwardExtensionBase
SET a422_rel_opportunityid = o.OpportunityId, a422_name = o.Name
FROM A422_AwardExtensionBase AS t
      INNER JOIN Temp_DuplicateOpportunities AS dups
            ON t.a422_rel_opportunityid = dups.DuplicateOppId
      INNER JOIN Opportunity AS o
            ON dups.PreferredOppId = o.OpportunityId
WHERE dups.Closed = 0

UPDATE t
SET ObjectId = o.OpportunityId
FROM AnnotationBase AS t
      INNER JOIN Temp_DuplicateOpportunities AS dups
            ON t.ObjectId = dups.DuplicateOppId
      INNER JOIN Opportunity AS o
            ON dups.PreferredOppId = o.OpportunityId
WHERE dups.Closed = 0

UPDATE Opportunity
SET EstimatedValue = 9999
FROM Opportunity AS dup
      INNER JOIN Temp_DuplicateOpportunities AS dups
      ON dups.DuplicateOppId = dup.OpportunityId
WHERE dups.Closed = 0

UPDATE Opportunity
SET EstimatedValue = 1000
FROM Opportunity AS bestopp
      INNER JOIN Temp_DuplicateOpportunities AS dups
      ON dups.PreferredOppId = bestopp.OpportunityId
WHERE dups.Closed = 0  

 

Create a saved query in CRM to find all opportunities with Estimated Value of 1000.  Create a workflow that 1) creates a merge activity and 2) updates requirements.

Run first activity update.

After review of merged opportunities, the duplicates can be deleted.

UPDATE dup
SET DeletionStateCode = 2
FROM Opportunity AS dup
      INNER JOIN Temp_DuplicateOpportunities AS dups
      ON dups.DuplicateOppId = dup.OpportunityId
WHERE dups.Closed = 0

UPDATE Temp_DuplicateOpportunities
SET Closed = 1

Comments