How to Clean Up 'Duplicate' Data While Preserving Most Recent Entry

Cleaning Up ‘Duplicate’ Data While Preserving Most Recent Entry

In this article, we will explore how to clean up data that appears as duplicates while preserving the most recent entry. This is a common problem in data analysis and can be achieved using SQL queries.

Understanding the Problem

The problem at hand involves displaying each crew member’s basic information and the most recent start date from their contracts. However, with a basic query, it returns a row for each contract, duplicating the basic information with distinct start and end dates. We need to display only one row per person, with the latest start date (or null if they have never had a start date).

The Query

The provided SQL query appears as follows:

SELECT 
    Case when P01.EMPLOYMENTENDDATE < getdate() then 'Y'
    else ''
end as "Deactivate",
concat(p01.FIRSTNAME,' ',p01.MIDDLENAME) as "First and Middle",
      p01.LASTNAME, 
p01.PIN,
(select top 1 TELENO FROM PW001P0T WHERE PIN = P01.PIN and TELETYPE = 6 ORDER BY TELEPRIORITY) as "EmailAddress",
      org.NAME AS Vessel, 
case 
       WHEN c02.CODECATEGORY= '20' then 'MARINE'
       WHEN c02.CODECATEGORY= '10' then 'MARINE'
       ELSE 'HOTEL' end as "Department",
         c02.name as RankName,
               c02.Alternative RankCode, 
convert(varchar, ACT.DATEFROM,101) EmbarkDate,
convert(varchar,(case when ACT.DATEFROM is null then p03.TODATEESTIMATED else ACT.DATEFROM end),101) DebarkDate

FROM   PW001P01 p01 
      JOIN PW001P03 p03 
           ON  p03.PIN = p01.PIN
      LEFT JOIN PW001C02 c02 
           ON  c02.CODE = p03.RANK 
         /*LEFT JOIN PW001C02 CCIRankTbl 
           ON  CCIRankTbl.CODE = p01.RANK*/
      LEFT JOIN PWORG org 
           ON  org.NUMORGID = dbo.ad_scanorgtree(p03.NUMORGID, 3) 
     LEFT JOIN PWORGVESACT ACT
     ON  ACT.numorgid=dbo.ad_scanorgtree(p03.numorgid,3) 

where P01.EMPLOYMENTENDDATE > getdate()-10 or P01.EMPLOYMENTENDDATE is null

The Issue

The issue with this query is that it returns multiple rows for each contract, even if the basic information remains the same but the dates change.

Solution 1: Using `SELECT DISTINCT`

One possible solution to this problem is to use the SELECT DISTINCT statement. However, since we have multiple columns that can be duplicated in some way, using a subquery or an aggregate function may not work as expected.

Here’s an example of how you could approach this:

SELECT 
    Case when P01.EMPLOYMENTENDDATE < getdate() then 'Y'
    else ''
end as "Deactivate",
concat(p01.FIRSTNAME,' ',p01.MIDDLENAME) as "First and Middle",
      p01.LASTNAME, 
p01.PIN,
select top 1 TELENO FROM PW001P0T WHERE PIN = P01.PIN and TELETYPE = 6 ORDER BY TELEPRIORITY) as "EmailAddress",
      org.NAME AS Vessel, 
case 
       WHEN c02.CODECATEGORY= '20' then 'MARINE'
       WHEN c02.CODECATEGORY= '10' then 'MARINE'
       ELSE 'HOTEL' end as "Department",
         c02.name as RankName,
               c02.Alternative RankCode, 
convert(varchar, (select top 1 A.DATEFROM FROM PWORGVESACT A WHERE A.numorgid = ACT.numorgid ORDER BY A.DATEFROM DESC) AS DebarkDate

FROM   PW001P01 p01 
      JOIN PW001P03 p03 
           ON  p03.PIN = p01.PIN
      LEFT JOIN PW001C02 c02 
           ON  c02.CODE = p03.RANK 
         /*LEFT JOIN PW001C02 CCIRankTbl 
           ON  CCIRankTbl.CODE = p01.RANK*/
      LEFT JOIN PWORG org 
           ON  org.NUMORGID = dbo.ad_scanorgtree(p03.NUMORGID, 3) 
     LEFT JOIN PWORGVESACT ACT
     ON  ACT.numorgid=dbo.ad_scanorgtree(p03.numorgid,3) 

where P01.EMPLOYMENTENDDATE > getdate()-10 or P01.EMPLOYMENTENDDATE is null

GROUP BY 
    Case when P01.EMPLOYMENTENDDATE < getdate() then 'Y'
    else ''
end as "Deactivate",
concat(p01.FIRSTNAME,' ',p01.MIDDLENAME) as "First and Middle",
      p01.LASTNAME, 
p01.PIN,
select top 1 TELENO FROM PW001P0T WHERE PIN = P01.PIN and TELETYPE = 6 ORDER BY TELEPRIORITY) as "EmailAddress",
      org.NAME AS Vessel, 
case 
       WHEN c02.CODECATEGORY= '20' then 'MARINE'
       WHEN c02.CODECATEGORY= '10' then 'MARINE'
       ELSE 'HOTEL' end as "Department",
         c02.name as RankName,
               c02.Alternative RankCode

This query groups the results by all columns except DebarkDate. However, since we want to preserve the most recent start date, this approach is not ideal.

Solution 2: Using a Subquery

A better approach would be to use a subquery to get the latest date for each contract. Here’s how you could do it:

SELECT 
    Case when P01.EMPLOYMENTENDDATE < getdate() then 'Y'
    else ''
end as "Deactivate",
concat(p01.FIRSTNAME,' ',p01.MIDDLENAME) as "First and Middle",
      p01.LASTNAME, 
p01.PIN,
(select top 1 TELENO FROM PW001P0T WHERE PIN = P01.PIN and TELETYPE = 6 ORDER BY TELEPRIORITY) as "EmailAddress",
      org.NAME AS Vessel, 
case 
       WHEN c02.CODECATEGORY= '20' then 'MARINE'
       WHEN c02.CODECATEGORY= '10' then 'MARINE'
       ELSE 'HOTEL' end as "Department",
         c02.name as RankName,
               c02.Alternative RankCode, 
convert(varchar,(select top 1 A.DATEFROM FROM PWORGVESACT A WHERE A.numorgid = ACT.numorgid ORDER BY A.DATEFROM DESC) AS DebarkDate

FROM   PW001P01 p01 
      JOIN PW001P03 p03 
           ON  p03.PIN = p01.PIN
      LEFT JOIN PW001C02 c02 
           ON  c02.CODE = p03.RANK 
         /*LEFT JOIN PW001C02 CCIRankTbl 
           ON  CCIRankTbl.CODE = p01.RANK*/
      LEFT JOIN PWORG org 
           ON  org.NUMORGID = dbo.ad_scanorgtree(p03.NUMORGID, 3) 
     LEFT JOIN PWORGVESACT ACT
     ON  ACT.numorgid=dbo.ad_scanorgtree(p03.numorgid,3) 

where P01.EMPLOYMENTENDDATE > getdate()-10 or P01.EMPLOYMENTENDDATE is null

GROUP BY 
    Case when P01.EMPLOYMENTENDDATE < getdate() then 'Y'
    else ''
end as "Deactivate",
concat(p01.FIRSTNAME,' ',p01.MIDDLENAME) as "First and Middle",
      p01.LASTNAME, 
p01.PIN,
(select top 1 TELENO FROM PW001P0T WHERE PIN = P01.PIN and TELETYPE = 6 ORDER BY TELEPRIORITY) as "EmailAddress",
      org.NAME AS Vessel, 
case 
       WHEN c02.CODECATEGORY= '20' then 'MARINE'
       WHEN c02.CODECATEGORY= '10' then 'MARINE'
       ELSE 'HOTEL' end as "Department",
         c02.name as RankName,
               c02.Alternative RankCode

Solution 3: Using `SELECT DISTINCT` and a `ROW_NUMBER()`

However, this approach still has limitations because we have multiple columns that can be duplicated. We need to use a more advanced approach.

One possible solution is to use the ROW_NUMBER() function in combination with SELECT DISTINCT. Here’s how you could do it:

SELECT 
    ROW_NUMBER() OVER(PARTITION BY 
        Case when P01.EMPLOYMENTENDDATE < getdate() then 'Y'
        else ''
     end as "Deactivate",
concat(p01.FIRSTNAME,' ',p01.MIDDLENAME) as "First and Middle",
      p01.LASTNAME, 
p01.PIN,
select top 1 TELENO FROM PW001P0T WHERE PIN = P01.PIN and TELETYPE = 6 ORDER BY TELEPRIORITY) as "EmailAddress",
      org.NAME AS Vessel, 
case 
       WHEN c02.CODECATEGORY= '20' then 'MARINE'
       WHEN c02.CODECATEGORY= '10' then 'MARINE'
       ELSE 'HOTEL' end as "Department",
         c02.name as RankName,
               c02.Alternative RankCode, 
convert(varchar,(select top 1 A.DATEFROM FROM PWORGVESACT A WHERE A.numorgid = ACT.numorgid ORDER BY A.DATEFROM DESC) AS DebarkDate

FROM   PW001P01 p01 
      JOIN PW001P03 p03 
           ON  p03.PIN = p01.PIN
      LEFT JOIN PW001C02 c02 
           ON  c02.CODE = p03.RANK 
         /*LEFT JOIN PW001C02 CCIRankTbl 
           ON  CCIRankTbl.CODE = p01.RANK*/
      LEFT JOIN PWORG org 
           ON  org.NUMORGID = dbo.ad_scanorgtree(p03.NUMORGID, 3) 
     LEFT JOIN PWORGVESACT ACT
     ON  ACT.numorgid=dbo.ad_scanorgtree(p03.numorgid,3) 

where P01.EMPLOYMENTENDDATE > getdate()-10 or P01.EMPLOYMENTENDDATE is null

GROUP BY 
    Case when P01.EMPLOYMENTENDDATE < getdate() then 'Y'
    else ''
end as "Deactivate",
concat(p01.FIRSTNAME,' ',p01.MIDDLENAME) as "First and Middle",
      p01.LASTNAME, 
p01.PIN,
select top 1 TELENO FROM PW001P0T WHERE PIN = P01.PIN and TELETYPE = 6 ORDER BY TELEPRIORITY) as "EmailAddress",
      org.NAME AS Vessel, 
case 
       WHEN c02.CODECATEGORY= '20' then 'MARINE'
       WHEN c02.CODECATEGORY= '10' then 'MARINE'
       ELSE 'HOTEL' end as "Department",
         c02.name as RankName,
               c02.Alternative RankCode

This query uses the ROW_NUMBER() function to assign a unique number to each row within each partition. Since all rows in a partition have the same values for the specified columns, we can use this number to identify the most recent date.

However, since we want to preserve the most recent start date for each contract, we need to get the latest date from the subquery as well.

Conclusion

In conclusion, using SELECT DISTINCT and a subquery or the ROW_NUMBER() function is one way to solve this problem. However, these approaches are limited by the columns that can be duplicated in some way. To achieve optimal results, we need to carefully consider the columns used for grouping and partitioning.

By analyzing the requirements of the query and understanding how the different functions work together, we can choose the best approach to get the desired results.

Recommendations

Based on our analysis, here are some recommendations:

Instead of using GROUP BY alone, use it in combination with other aggregate functions like ROW_NUMBER() or a subquery.
When grouping rows by multiple columns, consider using a more advanced function like LAG() or LEAD() to get the latest date for each contract.
If you need to group results based on specific conditions, use conditional aggregation (e.g., MAX() or MIN()) with a subquery or the ROW_NUMBER() function.

By following these recommendations and understanding how to effectively use aggregate functions in SQL, we can achieve optimal performance and accuracy for our queries.

Last modified on 2025-03-01