Select k.Subscriberssn,k.CompanyEmployeeId,k.FirstName,k.LastName,K.EmploymentType,K.EmployeeId,min(createdon) as CreatedDate from neptune..Employeehist e with(nolock)
inner join
(
Select distinct EE.SubscriberSSN,EE.EmployeeId,EE.CompanyEmployeeId,EE.FirstName,EE.LastName, EE.EmploymentType,max(EE_Hist.Createdon) as Createddate
from EmployeeHist As EE_Hist,Employee As EE
Where EE.TenantId ='0B836322-0441-E311-85DF-001F29DE211C' and cast(EE.SubscriberSSN as bigint)> 10000
and EE.EmploymentStatusCode<>'T'
and EE.Status=1
and EE.IsDummySSN=0
and EE_Hist.SubscriberSSN=EE.Subscriberssn
--and EE_Hist.EmploymentType<>EE.EmploymentType
and EE_Hist.EmploymentType='F' --and EE.EmploymentType='P'
Group by EE.SubscriberSSN,EE.FirstName,EE.LastName,EE.EmploymentType,EE_Hist.EmploymentType,EE.EmployeeId,EE.CompanyEmployeeId,EE.FirstName,EE.LastName
--Order by EE.CreatedOn desc
)k
on k.subscriberssn = e.subscriberssn
where e.companyid='' AND e.EmploymentType='P'
and e.Createdon>k.Createddate
Group by k.Subscriberssn,K.EmploymentType,k.EmployeeId,k.CompanyEmployeeId,k.FirstName,k.LastName