Clean up ‘duplicate’ data while preserving most recent entry

I want to display each crew member, basic info, and the most recent start date from their contracts. With my basic query, it returns a row for each contract, duplicating the basic info with a distinct start and end date. I only need one row per person, with the latest start date (or null if they have never yet had a start date).

I have limited understanding of group by and partition functions. Queries I have reverse engineered for similar date use partition and create temp tables where they select from. Ultimately I could reuse that but it seems more convoluted than what we need.

    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

I only need to show one row per column. The first 5 columns will be the same always. The last columns depend on contract, and we just need data from the most recent one.

<table><tbody><tr><th>Deactivate</th><th>First and Middle</th><th>Lastname</th><th>PIN</th><th>Email</th><th>Vessel</th><th>Department</th><th>Rank</th><th>RankCode</th><th>Embark</th><th>Debark</th></tr><tr><td> </td><td>Martin</td><td>Smith</td><td>123</td><td>msmith@fake.com</td><td>Ship1</td><td>Marine</td><td>ViceCaptain</td><td>VICE</td><td>9/1/2008</td><td>9/20/2008</td></tr><tr><td> </td><td>Matin</td><td>Smith</td><td>123</td><td>msmith@fake.com</td><td>Ship2</td><td>Marine</td><td>Captain</td><td>CAP</td><td>12/1/2008</td><td>12/20/2008</td></tr><tr><td> </td><td>Steve Mark</td><td>Dude</td><td>98765</td><td>sdude@fake.com</td><td>Ship1</td><td>Hotel</td><td>Chef</td><td>CHEF</td><td>5/1/2009</td><td>8/1/2009</td></tr><tr><td> </td><td>Steve Mark</td><td>Dude</td><td>98765</td><td>sdude@fake.com</td><td>Ship3</td><td>Hotel</td><td>Chef</td><td>CHEF</td><td>10/1/2010</td><td>12/20/2010</td></tr></tbody></table>

Answer

Change your query to a SELECT DISTINCT on the main query and use a sub-select for DebarkDate column:

(SELECT TOP 1 A.DATEFROM FROM PWORGVESACT A WHERE A.numorgid = ACT.numorgid ORDER BY A.DATEFROM DESC) AS DebarkDate

You can do whatever conversions on the date you need to from the result of that sub-query.

Advertisement

Answer