SELECT
Employee.Id Id,
Employee.Id ExtEmployeeId,
name.EffectiveDate,
SomeCol9,
SomeCol8,
employee.SomeLkpEmployeeGenderId,
ExeSomeCol10,
ExeCompanyId,
employee.SomeCol15,
name.FirstName,
name.LastName,
name.MiddleName,
name.Suffix,
status.SomeCol7,
status.SomeCol1,
status.Married,
status.SomeCol13,
status.SomeCol14,
status.Manager,
status.Officer,
status.SomeCol11,
status.Seasonal,
status.SomeCol5,
status.Student,
status.SomeCol6,
status.Title,
status.Probation,
status.SomeCol12,
address.Address1 + ' ' + address.Address1 + ' ' + address.City + ', ' + address.State + ' ' + address.Zip + ' ' + address.Country [Address],
address.Address1,
address.Address2,
address.City,
address.State,
address.Country,
address.Zip,
empaddress.SomeCol2,
OriginalId,
Employee.SomeCol3 SomeCol3
FROM Exe_Employee employee
LEFT JOIN
Exe_EmployeeName name
ON employee.id = name.ExeEmployeeId
LEFT JOIN
Exe_EmployeeStatus status
ON employee.id = status.ExeEmployeeId
LEFT JOIN
Exe_EmployeeAddress empaddress
on employee.Id = empaddress.ExeemployeeId
LEFT JOIN Exe_Address address
on empaddress.ExeAddressId = address.Id
Where
(name.SomeCol3 = 0
and name.EffectiveDate <= getdate()
and name.id = (SELECT TOP 1 ID FROM Exe_EmployeeName name2 WHERE SomeCol3 = 0
AND name2.ExeEmployeeId = name.ExeEmployeeId AND EffectiveDate <= getdate() ORDER BY effectiveDate DESC)
)
and
(
status.SomeCol3 = 0
and status.EffectiveDate <= getdate()
and status.id = (SELECT TOP 1 ID FROM Exe_EmployeeStatus Status2 WHERE SomeCol3 = 0
AND Status2.ExeEmployeeId = status.ExeEmployeeId AND EffectiveDate <= getdate() ORDER BY effectiveDate DESC)
)
and
(
empaddress.SomeCol3 = 0
and empaddress.EffectiveDate <= getdate()
and empaddress.id = (SELECT TOP 1 ID FROM Exe_EmployeeAddress Address2 WHERE SomeCol3 = 0
AND Address2.ExeEmployeeId = empaddress.ExeEmployeeId AND EffectiveDate <= getdate() ORDER BY effectiveDate DESC)
)
)