Understanding the Problem and Query

The provided query is an inner join of several tables in a database, specifically targeting data from the [Rez] schema. The goal is to retrieve duplicate rows based on specific fields (pe.[EMailAddress], pn.[FirstName], pn.[LastName], and p.[DOB]) within these joins.

To begin, let’s break down the query:

• Outer Query: This query selects data from the inner join of four tables: [Person], [PersonName], [Agent], and [PersonEMail]. The outer query utilizes a subquery (T1).

• Inner Join Tables:

  • [Person]: This table contains person-related information.
  • [PersonName]: Used to link persons with their respective names.
  • [Agent]: Relates agents to the people in the system, including agent roles.
  • [PersonEMail]: Maps each person’s email address.

• Conditions:

  • a.CreatedUTC > '2018-01-01': Filters data based on a “created UTC” timestamp greater than January 1st, 2018.

The question then asks to identify duplicate rows based on the specified fields (pe.[EMailAddress], pn.[FirstName], pn.[LastName], and p.[DOB]) within these joins. In other words, we want to find all records that share the same values for these four fields.

The Solution

To accomplish this task, you can employ a technique known as “row number” or “rank” in SQL queries. This involves using a subquery with ROW_NUMBER function to assign a unique rank to each row within the specified group (in this case, rows sharing the same values for pe.[EMailAddress], pn.[FirstName], pn.[LastName], and p.[DOB]).

Hugo Markdown Code Block:

SELECT * FROM
(
  -- This subquery assigns a unique rank to each row within its group.
  SELECT T1.*, 
         COUNT(*) OVER (PARTITION BY 
                        T1.CustomerNumber,
                        T1.Title,
                        T1.FirstName,
                        T1.[LastName],
                        T1.[AgentID],
                        T1.[AgentName],
                        T1.[PersonID],
                        T1.[EMailAddress]
) As DuplicateRowCount

FROM
     (
      -- The original inner query.
      SELECT p.CustomerNumber
          ,pn.[Title]
          ,pn.[FirstName]
          ,pn.[LastName]
          ,a.[AgentID]
          ,a.[AgentName]
          ,a.[PersonID]
          ,pe.[EMailAddress]
          ,(SELECT TOP 1 pp.[PhoneCode] FROM [Rez].[PersonPhone] pp 
           WHERE pp.PersonID = p.PersonID ORDER BY pp.ModifiedUTC DESC) AS Phone
           FROM [Rez].[Person] p
           INNER JOIN [Rez].[PersonName] pn ON p.PersonID = pn.PersonID
           INNER JOIN [Rez].[Agent] a ON a.PersonID = p.PersonID
           INNER JOIN [Rez].[PersonEMail] pe ON pe.PersonID = p.PersonID
           INNER JOIN [Rez].[AgentRole] ar ON ar.[AgentID] = a.[AgentID]
           WHERE a.CreatedUTC > '2018-01-01'
     ) T1
) T2
WHERE T2.DuplicateRowCount > 1
ORDER BY T2.[EMailAddress], T2.[FirstName], T2.[LastName], T2.[DOB]

This solution provides the required data while excluding duplicates based on the specified fields.

Limitations of This Approach

• The above approach assumes that the pe.[EMailAddress], pn.[FirstName], pn.[LastName], and p.[DOB] values must be present for every row in order to identify duplicates. However, there might be cases where these conditions aren’t met.

An Alternative Approach

To address such edge cases, you could use the CASE WHEN function within a subquery to assign a flag (e.g., 0 or 1) indicating whether each record shares values with other records based on the specified fields.

For instance:

SELECT * FROM
(
  SELECT T1.*, 
         CASE 
           WHEN EXISTS (
             SELECT 1
             FROM [Rez].[PersonName] pn2
             INNER JOIN [Rez].[Agent] a2 ON a2.PersonID = pn2.PersonID
             INNER JOIN [Rez].[PersonEMail] pe2 ON pe2.PersonID = a2.PersonID
             WHERE 
               pe2.[EMailAddress] = T1.[EMailAddress]
               AND pn2.[FirstName] = T1.[FirstName]
               AND pn2.[LastName] = T1.[LastName]
               AND T1.[DOB] = pe2.[DOB]
           ) THEN 1 ELSE 0 END AS Shared
  FROM [Rez].[Person] p
  INNER JOIN [Rez].[PersonName] pn ON p.PersonID = pn.PersonID
  INNER JOIN [Rez].[Agent] a ON a.PersonID = p.PersonID
  INNER JOIN [Rez].[PersonEMail] pe ON pe.PersonID = p.PersonID
  INNER JOIN [Rez].[AgentRole] ar ON ar.[AgentID] = a.[AgentID]
  WHERE a.CreatedUTC > '2018-01-01'
) T1
WHERE T1.Shared = 1
ORDER BY T1.[EMailAddress], T1.[FirstName], T1.[LastName], T1.[DOB]

This revised approach aims to ensure that only records sharing the same values for all specified fields are included in the results, effectively addressing any potential edge cases.

Last modified on 2024-01-03