Home Forums SQL Server 2012 SQL Server 2012 - T-SQL How can I group near duplicate records under a new common field? RE: How can I group near duplicate records under a new common field?

  • August 27, 2015 at 7:39 pm

    #1822949

    craig.bobchin (8/27/2015)

    ScottPletcher (8/27/2015)

    To me it seems fairly easy to match single rows. Maybe you can just output those matches to a table and then consolidate the like ids using that result.

    SELECT m1.dimMemberId, m2.dimMemberId

    FROM dimMember m1

    INNER JOIN dimMember m2 ON

    m1.dimMemberId < m2.dimMemberId AND

    CASE WHEN LEFT(m1.FirstName, 3) = LEFT(m2.FirstName, 3) THEN 1 ELSE 0 END +

    CASE WHEN m1.LastName = m2.LastName THEN 1 ELSE 0 END +

    CASE WHEN m1.DOB = m2.DOB THEN 1 ELSE 0 END +

    CASE WHEN m1.CurrentAddress1 = m2.CurrentAddress1 THEN 1 ELSE 0 END +

    CASE WHEN m1.MemberCode = m2.MemberCode THEN 1 ELSE 0 END +

    CASE WHEN m1.SSN = m2.SSN THEN 1 ELSE 0 END +

    CASE WHEN m1.SubscriberCode = m2.SubscriberCode THEN 1 ELSE 0 END

    >= 4

    This is pretty close to what I'm looking for. I added your join login in the select as a new column called Score. Now I just need to figure out how to assign a common value to a new field based on the matches.

    Scott did it pretty much as I described including being able to eliminate roughly half the work by using a Triangular Join instead of a Full (Square) Cartesian Join.

    The question now is, what do you mean by a "common value"?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)