Detecting Duplicates by Reference Code

Detecting Duplicates by Reference Code

In some scenarios, you need to ensure that a reference code is completely unique among a record type.

 
To do this, use the following SQL script:

  1. --Look for duplicate records in the __M_Matters table
  2. SELECT
  3.     *
  4. FROM
  5.     --Change this to the table we want to identify duplicates in
  6.     __M_Matters V1,
  7.     (
  8.         SELECT
  9.             Final_ReferenceCode,
  10.             COUNT(*) as Duplicates
  11.         FROM
  12.             __M_Matters --This needs to match the same table as above
  13.         GROUP BY
  14.             Final_ReferenceCode
  15.         HAVING 
  16.             COUNT(*) > 1
  17.      ) V2
  18. WHERE 1=1
  19.     AND V1.Final_ReferenceCode = V2.Final_ReferenceCode
  20. ORDER BY
  21.     V1.Final_ReferenceCode





    • Related Articles

    • Matters: Trimming Client Numbers from Matter Numbers

      The following script will peel off client numbers from matter numbers. For example, if a matter has a ReferenceCode like: ABC-0001 After the script runs, it will have a reference code of: 0001 Scenario 1: There are Client Reference Codes In some ...

    • Deduplicate Matter Participating Entities

      Some systems will have the same entity (contact, user, etc...) associated with a matter multiple times with exactly the same role. If the signature of the relationship is exactly the same, the following script will eliminate the duplicates. ...

    • Audit Scripts: Row Counts and Progress Monitoring

      This article shows you a script that can be used to monitor the progress of both a Backup and a Restore. It can also be used to just review row counts on all the Universal Database tables. The first code block is used to populate a SQL Server Temp ...

    • Convert User-Select/Contact-Select Custom Fields => Participants

      Some systems do not support User-Select or Contact Select custom fields. The following query will transform User-Select fields into Matter Participants. This script can easily be adapted for Contact-Select fields. -- Transform Custom Fields on ...

    • Other: Creating Unique Display Numbers

      The following script will create unique Final_ReferenceCode for all Contacts. It can be easily modified to apply to other record types. Please note that the Final_ReferenceCode that are generated are based on the data in the database. If you are ...