MSSQL: Get percentage of match based on criteria in query

user_15265941 0
Viewed 29

I have a query which searches for duplicates on my database. I am searching based on firstname, lastname, and date of birth. What I want to do is to show some percentage, so if it matches with all three it will show 100%, if it matches with just the firstname and last name, it should say 66% etc.. I am not sure how I can go about this.

I am doing this on Microsoft SQL

Below is my current query:

SELECT DISTINCT t.CUSTID, t.FORENAME, t.SURNAME, t.GENDER, CONVERT(varchar, t.DATE_BIRTH, 103) as 
DOB 
    FROM CUSTOMERS AS t 
    INNER JOIN (
            SELECT FORENAME, SURNAME, DATE_BIRTH
            FROM CUSTOMERS
            GROUP BY FORENAME, SURNAME, DATE_BIRTH
            HAVING COUNT(*) > 1) AS td 
        ON t.FORENAME = td.FORENAME AND t.SURNAME = td.SURNAME
            AND t.DATE_BIRTH = td.DATE_BIRTH
sql-server sql
1 Answers

You can use something like this:

select c.*, c2.*,
       ( (case when c.forename = c2.forename then 1.0 else 0 end) +
         (case when c.surname = c2.surname then 1.0 else 0 end) +
         (case when c.date_of_birth = c2.date_of_birth then 1.0 else 0 end)
       ) / 3 as ratio
from customers c join
     customers c2
     on c.forename = c2.forename or
        c.surname = c2.surname or
        c.date_of_birth = c2.date_of_birth
order by ratio desc;

This will not be efficient. Also, every customer will be compared to itself so the result set will be large.

user_11440350
Related