Dedup and Combine Customer Data

Question

I'm trying to combine records based on the ClusterID to have an enriched data of customer records.

How will I be able to group the following using MS SQL? Coalesce won't work as records need to be on the same row to make it work, and if I have more than 2 matches per cluster it's going to be a tedious processing. Using max by ClusterId in all columns is a workaround I'm doing but I was hoping there is a more efficient way to do this.

Have:

ClusterID,CustomerNo,Name,Email,Mobile,Address,PostalCode,Passport,ProfileNo
100,NULL,Person,person@email.com,NULL,OfficeAdd,12345,NULL,123
100,456,Person,person@email.com,98765,HomeAdd,34567,P12345,NULL

**This is a result of the SSIS DQS Matching node (https://ssisdqsmatching.codeplex.com/). It can do the match, but cannot handle the survivorship portion to get the golden record.

Want:

ClusterID,CustomerNo,Name,Email,Mobile,Address,PostalCode,Passport,ProfileNo
100,456,Person,person@email.com,98765,OfficeAdd,12345,P12345,123

Any thoughts would be much appreciated. Thank you!

score 1 · Answer 1 · answered Aug 15 '16 at 05:46

DECLARE @CLUSTERID VARCHAR(MAX),@CUSTOMERNO VARCHAR(MAX),@NAME VARCHAR(MAX),@EMAIL VARCHAR(MAX),@MOBILE VARCHAR(MAX)
DECLARE @POSTALCODE VARCHAR(MAX),@PASSPORT VARCHAR(MAX),@PROFILENO VARCHAR(MAX),@ADDRESS VARCHAR(MAX)

DECLARE @NCLUSTERID VARCHAR(MAX),@NCUSTOMERNO VARCHAR(MAX),@NNAME VARCHAR(MAX),@NEMAIL VARCHAR(MAX),@NMOBILE VARCHAR(MAX)
DECLARE @NPOSTALCODE VARCHAR(MAX),@NPASSPORT VARCHAR(MAX),@NPROFILENO VARCHAR(MAX),@NADDRESS VARCHAR(MAX)
DECLARE @NEW_TABLE TABLE (  ClusterID varchar(max) ,
    CustomerNo varchar(max) ,
    Name varchar(max) ,
    Email varchar(max) ,
    Mobile varchar(max) ,
    Address varchar(max) ,
    PostalCode varchar(max) ,
    Passport varchar(max) ,
    ProfileNo varchar(max) 
)

DECLARE C CURSOR FOR
SELECT DISTINCT CLUSTERID FROM CUSTOMER
OPEN C
FETCH NEXT FROM C INTO @CLUSTERID
WHILE @@FETCH_STATUS=0
BEGIN

    DECLARE D CURSOR FOR
    select CustomerNo,Name,Email,Mobile,Address,PostalCode,Passport,ProfileNo from customer where ClusterID=@CLUSTERID
    OPEN D
    FETCH NEXT FROM D INTO @CustomerNo,@Name,@Email,@Mobile,@Address,@PostalCode,@Passport,@ProfileNo
    WHILE @@FETCH_STATUS=0
    BEGIN   

    IF @CustomerNo is not null SET  @NCustomerNo=@CustomerNo
    IF @CustomerNo IS NOT NULL SET  @NCustomerNo= @CustomerNo ;
    IF @Name       IS NOT NULL SET  @NName      = @Name       ;
    IF @Email      IS NOT NULL SET  @NEmail     = @Email      ;
    IF @Mobile     IS NOT NULL SET  @NMobile    = @Mobile     ;
    IF @Address    IS NOT NULL SET  @NAddress   = @Address    ;
    IF @PostalCode IS NOT NULL SET  @NPostalCode= @PostalCode ;
    IF @Passport   IS NOT NULL SET  @NPassport  = @Passport   ;
    IF @ProfileNo  IS NOT NULL SET  @NProfileNo = @ProfileNo  ; 

    FETCH NEXT FROM D INTO @CustomerNo,@Name,@Email,@Mobile,@Address,@PostalCode,@Passport,@ProfileNo
    END
    CLOSE D
    DEALLOCATE D
        INSERT INTO @NEW_TABLE VALUES (
         @CLUSTERID,
         @NCustomerNo   ,
         @NName         ,
         @NEmail        ,
         @NMobile       ,
         @NAddress      ,
         @NPostalCode   ,
         @NPassport     ,
         @NPROFILENO    
        )



FETCH NEXT FROM C INTO @CLUSTERID
END
CLOSE C
DEALLOCATE C


SELECT * FROM @NEW_TABLE

Thanks! This is something I'm looking for. Am I correct that it goes to a loop for each ClusterID and then goes fill up the fields when they aren't NULL? — mtryingtocode, Aug 15 '16 at 09:59
Also, to add some complication on the loop, let's say for the address field, row 1 address is not null and postal is null, then row 2 address and postal is not null. How can I add a condition that I will not take the postal code in the 2nd row? Since if I pick up postal code in row 2 it gives me incorrect information already when combined with row 1 address. — mtryingtocode, Aug 15 '16 at 10:00

score 0 · Answer 2 · answered Aug 16 '16 at 07:34

I guess this query solves your requirement

DECLARE @CLUSTERID VARCHAR(MAX),@CUSTOMERNO VARCHAR(MAX),@NAME VARCHAR(MAX),@EMAIL VARCHAR(MAX),@MOBILE VARCHAR(MAX)
DECLARE @POSTALCODE VARCHAR(MAX),@PASSPORT VARCHAR(MAX),@PROFILENO VARCHAR(MAX),@ADDRESS VARCHAR(MAX)

DECLARE @NCLUSTERID VARCHAR(MAX)=NULL,@NCUSTOMERNO VARCHAR(MAX)=NULL,@NNAME VARCHAR(MAX)=NULL,@NEMAIL VARCHAR(MAX)=NULL,@NMOBILE VARCHAR(MAX)=NULL
DECLARE @NPOSTALCODE VARCHAR(MAX)=NULL,@NPASSPORT VARCHAR(MAX)=NULL,@NPROFILENO VARCHAR(MAX)=NULL,@NADDRESS VARCHAR(MAX)=NULL
DECLARE @NEW_TABLE TABLE (  ClusterID varchar(max) ,
    CustomerNo varchar(max) ,
    Name varchar(max) ,
    Email varchar(max) ,
    Mobile varchar(max) ,
    Address varchar(max) ,
    PostalCode varchar(max) ,
    Passport varchar(max) ,
    ProfileNo varchar(max) 
)

DECLARE C CURSOR FOR
SELECT DISTINCT CLUSTERID FROM CUSTOMER
OPEN C
FETCH NEXT FROM C INTO @CLUSTERID
WHILE @@FETCH_STATUS=0
BEGIN

    DECLARE D CURSOR FOR
    select CustomerNo,Name,Email,Mobile,Address,PostalCode,Passport,ProfileNo from customer where ClusterID=@CLUSTERID
    OPEN D
    FETCH NEXT FROM D INTO @CustomerNo,@Name,@Email,@Mobile,@Address,@PostalCode,@Passport,@ProfileNo
    WHILE @@FETCH_STATUS=0
    BEGIN   

    IF @CustomerNo is not null SET  @NCustomerNo=@CustomerNo
    IF @CustomerNo IS NOT NULL SET  @NCustomerNo= @CustomerNo ;
    IF @Name       IS NOT NULL SET  @NName      = @Name       ;
    IF @Email      IS NOT NULL SET  @NEmail     = @Email      ;
    IF @Mobile     IS NOT NULL SET  @NMobile    = @Mobile     ;
    IF @Passport   IS NOT NULL SET  @NPassport  = @Passport   ;
    IF @ProfileNo  IS NOT NULL SET  @NProfileNo = @ProfileNo  ; 

    IF (@ADDRESS IS NOT NULL AND @NADDRESS IS NOT NULL)
    BEGIN
    SET @NAddress   = @Address    ;
    SET  @NPostalCode= @PostalCode ;
    END
    ELSE IF(@ADDRESS IS NOT NULL AND @NADDRESS IS NULL)
    BEGIN
    SET @NAddress   = @Address    ;
    SET  @NPostalCode= NULL ;
    END
    ELSE IF(@ADDRESS IS NULL AND @NADDRESS IS NOT NULL)
    BEGIN
    SET @NAddress   = NULL    ;
    SET  @NPostalCode= @PostalCode ;
    END


    FETCH NEXT FROM D INTO @CustomerNo,@Name,@Email,@Mobile,@Address,@PostalCode,@Passport,@ProfileNo
    END
    CLOSE D
    DEALLOCATE D

        INSERT INTO @NEW_TABLE VALUES (
         @CLUSTERID,
         @NCustomerNo   ,
         @NName         ,
         @NEmail        ,
         @NMobile       ,
         @NAddress      ,
         @NPostalCode   ,
         @NPassport     ,
         @NPROFILENO    
        )



FETCH NEXT FROM C INTO @CLUSTERID
END
CLOSE C
DEALLOCATE C


SELECT * FROM @NEW_TABLE

Dedup and Combine Customer Data

2 Answers2