CODEWITHSUNDEEP
sql-serverssisfuzzy-searchfuzzy-comparison
LearnByReading
LearnByReading

Reputation: 1883

SSIS Fuzzy Lookup for dates

I am trying to match clients using three fields: FirstName, LastName, Gender, and DOB. It's my understanding that SSIS Fuzzy Lookup only applies to strings (never seen this in documentations, just some bloggers said so) so in this scenario, could I somehow use DOB to build "confidence"? How does this tool compare the dates?

Here is a thread on this issue: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/d2e53f8c-abfc-461e-9263-fd46b95247c0/ssis-fuzzy-lookup-and-dates?forum=sqlintegrationservices

Upvotes: 1

Views: 761

Answers (1)

Robert Paulsen
Robert Paulsen

Reputation: 5151

I do a lot of Name/DOB searches and it's really difficult to get right. You can consider sorting using a function similar to the one below. This will basically take two 8-digit dates (YYYYMMDD) and count the number of miss-matched digits.

For example these date pairs have 1 miss-matched digit:

  • 1976-10-20 and 1976-01-20 (10 vs 01)
  • 1976-10-20 and 1975-10-20 (76 vs 75)

Depending on how much data dealing with, this is good for sorting (aka putting your best match first) but not for filtering (because there is no indexing involved).

ALTER FUNCTION dbo.BirthDateRank ( @DOB1 DATE, @DOB2 DATE )
RETURNS INT
AS
    BEGIN
        -- 10/31/2016 --> 10312016
        DECLARE @xDOB1 VARCHAR(10) = REPLACE(CONVERT(CHAR(10), @DOB1, 101), '/', '');
        DECLARE @xDOB2 VARCHAR(10) = REPLACE(CONVERT(CHAR(10), @DOB2, 101), '/', '');

        DECLARE @i INT = 0;
        DECLARE @iRet INT = 0;

        WHILE @i <= LEN(@xDOB1)
        BEGIN
            SET @iRet = @iRet + CASE WHEN SUBSTRING(@xDOB1, @i, 1) = SUBSTRING(@xDOB2, @i, 1) THEN 0 ELSE 1 END

            SET @i = @i + 1
        END

        RETURN @iRet;

    END;
GO

Upvotes: 2

Related Questions