Using SQL and Matillion for Employee table, I need to pull only the most recent employee hire date-CodePudding

This is the code I wrote in SSMS which works fine for my purposes however trying to use the same principles with Matillion (yes I have to use Matillion at my company) I am still getting duplicate employees but cannot simply use Distinct as if they quit and are rehired the hire dates are different as well as being assigned a new employee Id. Im just trying to make an employee summary table with only one entry per person using their most recent hire date and excluding the prior, Here is what I have in SSMS.

SELECT 

[PersonId] as 'Employee Id'
,[LastName] as 'Last Name'
  

    ,[FirstName] as 'First Name'
      ,[MiddleName] as 'Middle Name'
      ,[KnownAs] as 'Goes By'
      ,[HireDate] as 'Hire Date'
      ,[Address1] as 'Address'
      ,[Address2] as 'Address Contd'
      ,[City] as 'City'
      ,[State] as 'State'
      ,[Zip] as 'Zip Code'
      ,[EmployeeStatus] as 'Employee Status'
      ,[EmployeeType] as 'Employee Type'
      ,[BusinessTitle] as 'Business Title'
      ,[DateofBirth] as 'Date of Birth'
      ,[Gender] as 'Gender'
      ,[HomePhone] as 'Phone Number'
      ,[ReportsToName] as 'Supervisor Name'
      ,[SupervisorId] as 'Supervisor Id'
      ,[EmailAddress] as 'Email'
      ,[JobDescription] as 'Job Description'
      ,[DepartmentNumber] as 'Dept Number'
      ,[RaceEthnicty] as 'Race/Ethnicity'
      ,[PayRate] as 'Pay Rate'
      ,[FullPartTime] as 'Full / Part Time'
      ,[PayGroup] as 'Pay Group'
      ,[SSN] as 'SSN (encrypted)'
      ,[jobcode] as 'Job Code'

FROM [SuccessFactorsCensus].[dbo].[Census] C
WHERE HireDate=(SELECT MAX(HireDate) 
FROM [SuccessFactorsCensus].[dbo].[Census]
WHERE PersonId = C.PersonId)

CodePudding user response：

This will produce a query that gives equivalent results to what you're doing above:

For the subquery:

Table Input from [SuccessFactorsCensus].[dbo].[Census]
Aggregate component - add Grouping on PersonId, add Aggregate of Max on column HireDate

For the main query:

Table Input from [SuccessFactorsCensus].[dbo].[Census]
Join component
- use input above as "Main Table"
- join to output of Aggregate component
- Join type: Left
- Join Expression: x.PersonId = y.PersonId (replace x/y with the aliases you specified)

HOWEVER: If employees get a new PersonId every time they start and some are re-hired multiple times then you can't use PersonId to link the records of employees that are hired multiple times. You need to use some other attribute, e.g. SSN would be a great option as it should be the same for each employee every time they are hired. If you don't have access to that (e.g. it's encrypted) then you can try using email address or name address to try and get a list of unique persons. You need to solve this issue first before you can try getting the max HireDate for each person.