I am trying to create a report that display potential duplicate records based on three criteria: the last 4 of an SSN, last name and DOB. I posted a question here on the issue and received an answer that I should be using a Cross Apply to unpivot the data. The query runs fast and the results look better than my original query.
The new query is below and I have added a filter to show two examples that I am seeing occur across the data:
DECLARE
@StartDate DATE = '1/1/2017',
@EndDate DATE = '3/1/2017';
WITH
CTE
AS
(
SELECT
DENSE_RANK() OVER (ORDER BY c.socialSecurityNumber) AS [SSNRanking] ,
c.id AS [CustomerID] ,
c.socialSecurityNumber AS [SSN],
c.firstName AS [FirstName] ,
c.lastName AS [LastName] ,
c.birthDate [BirthDate] ,
c.emailAddress AS [EmailAddress],
c.createDate AS [CreateDate] ,
MAX(co.orderDate) AS [LastOrderDate] ,
ca.street1 AS [Addr1] ,
ca.city AS [City] ,
ca.stateAndTerritoriesID AS [State],
ca.zipCode5 AS [Zip] ,
c2.id AS [DupCustomerID] ,
c2.socialSecurityNumber AS [DupSSN] ,
c2.firstName AS [DupFirstName] ,
c2.lastName AS [DupLastName] ,
c2.birthDate AS [DupBirthDate] ,
c2.emailAddress AS [DupEmailAddress] ,
c2.createDate AS [DupCreateDate] ,
MAX(co.orderDate) AS [DupLastOrderDate] ,
ca.street1 AS [DupAddr1] ,
ca.city AS [DupCity],
ca.stateAndTerritoriesID AS [DupState] ,
ca.zipCode5 AS [DupZip]
FROM
dbo.Customers AS [c]
INNER JOIN dbo.Customers AS [c2] ON ( SUBSTRING(c.socialSecurityNumber,6,4) = SUBSTRING(c2.socialSecurityNumber,6,4) AND c.birthDate = c2.birthDate AND c.lastName = c2.lastName AND c.id <> c2.id )
INNER JOIN dbo.CustomerAddresses AS [ca] ON c.id = ca.customerID
--INNER JOIN dbo.CustomerAddresses AS [ca2] ON ca2.customerID = c2.id
LEFT OUTER JOIN dbo.Common_Orders AS [co] ON co.customerID = c.id
WHERE
c.customerStatusTypeID <> 'M'
AND c2.customerStatusTypeID <> 'M'
AND ca.addressType = 'M'
--AND ca2.addressType = 'M'
AND c.mergedTo IS NULL
AND c2.mergedTo IS NULL
AND CAST(co.orderDate AS DATE) >= @StartDate
AND CAST(co.orderDate AS DATE) <= @EndDate
AND ( c.id = 1545229 OR c.id = 2020489 )
GROUP BY
c.id ,
c.socialSecurityNumber ,
c.firstName ,
c.lastName ,
c.birthDate ,
c.emailAddress ,
c.createDate ,
ca.street1 ,
ca.city ,
ca.stateAndTerritoriesID ,
ca.zipCode5 ,
c2.id ,
c2.socialSecurityNumber ,
c2.firstName ,
c2.lastName ,
c2.birthDate ,
c2.emailAddress ,
c2.createDate ,
ca.street1 ,
ca.city ,
ca.stateAndTerritoriesID ,
ca.zipCode5
)
SELECT CA.CustomerID,
CA.SSNRanking ,
CA.SSN ,
CA.FirstName,
CA.LastName,
CA.BirthDate,
CA.EmailAddress,
CA.CreateDate ,
CA.LastOrderDate ,
CA.Addr1,
CA.City,
CA.[State],
CA.Zip
FROM
CTE
CROSS APPLY
(
VALUES
(CTE.SSNRanking, CTE.CustomerID, CTE.SSN, CTE.FirstName, CTE.LastName, CTE.Birthdate, CTE.EmailAddress, CTE.CreateDate, CTE.LastOrderDate, CTE.Addr1, CTE.City, CTE.[State], CTE.Zip),
(CTE.SSNRanking, CTE.DupCustomerID, CTE.DupSSN, CTE.DupFirstName, CTE.DuplastName, CTE.DupBirthDate, CTE.DupEmailAddress, CTE.DupCreateDate, CTE.DupLastOrderDate, CTE.DupAddr1, CTE.DupCity, CTE.DupState, CTE.DupZip)
) AS CA (SSNRanking, CustomerID, SSN, FirstName, LastName, BirthDate, EmailAddress, CreateDate, LastOrderDate, Addr1, City, [State], Zip)
ORDER BY CAST(CA.SSN AS INT) ASC, CA.CustomerID;
And the result set looks like (Original Image):
+ ---------- + ---------- + --------- + ---------- + -------- + ---------- + ------------ + --------------------- + ----------------------- + --------------------- + ------------ + ----- + ----- +
| CustomerId | SSNRanking | SSN | FirstName | LastName | BirthDate | EmailAddress | CreateDate | LastOrderDate | Addr1 | City | State | Zip |
+ ---------- + ---------- + --------- + ---------- + -------- + ---------- + ------------ + --------------------- + ----------------------- + --------------------- + ------------ + ----- + ----- +
| 1545229 | 1 | 000000000 | Aquia Boat | SALES | 1900-01-01 | null | 2013-05-28 00:00:00.0 | 2017-01-23 11:08:30.723 | 236 Willow Landing Rd | Stafford | VA | 22554 |
| 1545229 | 1 | 000000000 | Aquia Boat | SALES | 1900-01-01 | null | 2013-05-28 00:00:00.0 | 2017-01-06 12:31:15.370 | 11963 Jefferson Ave | Newport News | VA | 23606 |
| 2020489 | 1 | 000000000 | DIXIE | SALES | 1900-01-01 | null | 2017-01-06 12:27:56.5 | 2017-01-06 12:31:15.370 | 11963 Jefferson Ave | Newport News | VA | 23606 |
| 2020489 | 1 | 000000000 | DIXIE | SALES | 1900-01-01 | null | 2017-01-06 12:27:56.5 | 2017-01-23 11:08:30.723 | 236 Willow Landing Rd | Stafford | VA | 22554 |
+ ---------- + ---------- + --------- + ---------- + -------- + ---------- + ------------ + --------------------- + ----------------------- + --------------------- + ------------ + ----- + ----- +
And I see that the last four of the SSN, last name and DOB all match. But then I noticed duplicate Customer IDs Aqua Boats has an entry with the Dixie Sales address and Dixie Sales has an entry for Aqua Boats address - which I should not have and I looked in the Customer /Customer Addresses table at the particular accounts:
SELECT c.id, c.socialSecurityNumber, c.firstName, c.lastName, c.birthDate, ca.street1
FROM dbo.Customers AS c
INNER JOIN dbo.CustomerAddresses AS [ca] ON ca.customerID = c.id
WHERE c.id IN (1545229,2020489) AND ca.addressType = 'M';
And the results are here (Original Image):
+ ---------- + -------------------- + ---------- + -------- + ---------- + --------------------- +
| id | socialSecurityNumber | firstName | lastName | birthDate | street 1 |
+ ---------- + -------------------- + ---------- + -------- + ---------- + --------------------- +
| 1545229 | 000000000 | Aquia Boat | SALES | 1900-01-01 | 236 Willow Landing Rd |
| 2020489 | 000000000 | DIXIE | SALES | 1900-01-01 | 11963 Jefferson Ave |
+ ---------- + -------------------- + ---------- + -------- + ---------- + --------------------- +
When I run the query inside the CTE, I added two additional filters:
AND c.id <> ca2.customerID
AND c2.id <> ca.customerID
And the dataset looks like I want (Original Image):
+ ---------- + ---------- + --------- + ---------- + -------- + ---------- + --------------------- + ------------ + ----- + ----- + ------------- + --------- + ------------- + ----------- + ------------- + --------------------- + ------------ + -------- + ------ +
| SSNRanking | CustomerID | SSN | FirstName | LastName | BirthDate | Addr1 | City | State | Zip | DupCustomerID | DupSSN | DupFirstName | DupLastName | DupBirthDate | DupAddr1 | DupCity | DupState | DupZip |
+ ---------- + ---------- + --------- + ---------- + -------- + ---------- + --------------------- + ------------ + ----- + ----- + ------------- + --------- + ------------- + ----------- + ------------- + --------------------- + ------------ + -------- + ------ +
| 1 | 1545229 | 000000000 | Aquia Boat | SALES | 1900-01-01 | 236 Willow Landing Rd | Stafford | VA | 22554 | 2020589 | 000000000 | DIXIE | SALES | 1900-01-01 | 236 Willow Landing Rd | Stafford | VA | 22554 |
| 1 | 2020489 | 000000000 | DIXIE | SALES | 1900-01-01 | 11963 Jefferson Ave | Newport News | VA | 23606 | 1545229 | 000000000 | AQUIA BOAT | SALES | 1900-01-01 | 11963 Jefferson Ave | Newport News | VA | 23606 |
+ ---------- + ---------- + --------- + ---------- + -------- + ---------- + --------------------- + ------------ + ----- + ----- + ------------- + --------- + ------------- + ----------- + ------------- + --------------------- + ------------ + -------- + ------ +
Can I prevent the CROSS APPLY from creating additional records for customers with mailing address assigned to another customer?
Thanks,
