Hi. I am having difficulty getting the last/latest record from table.
These are the structure of the tables
[
TABLE RESULT]
I need to place it in the subquery.
select EmployeeNo
,(Select PosCode from EmploymentHistory where EmployeeNo=EmpPersonalInfo.EmployeeNo) as PositionCode
,(Select Rate from EmployeeSalaryHistory where EmployeeCode=EmpPersonalInfo.EmployeeNo) as PositionCode
from EmpPersonalInfo where EmployeeCode=902575
I can distinguish the latest record in both records if Status=1, what if status=0 for all the records in the table, how can I get the latest record (it depends on the effectivitydatefrom- for EmploymentHistory table, and EffectivityDate -for EmployeeSalaryHistory)
Aside from getting TOP 1 and ORDER BY DESC, is there anyway to do this in most suggested way?
What I have tried:
I tried the codes above but i am seeking for additional information.
AND I TRIED THIS, but i cant build the logic, can I manipulate it here?
select EmpPersonalInfo.LastName,EmpPersonalInfo.FirstName,EmpPersonalInfo.MiddleName,EmpPersonalInfo.MI,
EmploymentInfo.Street,EmploymentInfo.District,EmploymentInfo.City,EmploymentInfo.Country,EmploymentInfo.ZipCode
,position.PosTitle,tblEmployeeMainInfo.BranchCode,EmpDepartment.Department,tblEmployeeMainInfo.DivisionCode
,tblEmployeeMainInfo.AssignmentCode,EmploymentInfo.HireDate
,EmployeeSalaryHistory.EffectivityDate,EmployeeSalaryHistory.Rate
,EmployeeSalaryHistory.RateType,EmployeeSalaryHistory.Active as SalaryStatus,
EmploymentHistory.EffectivityDateFrom,EmploymentHistory.EffectivityDateTo,
EmploymentHistory.Status as EmploymentStatus
from EmpPersonalInfo LEFT JOIN EmploymentHistory ON EmpPersonalInfo.EmployeeNo=EmploymentHistory.EmployeeNo
LEFT JOIN EmployeeSalaryHistory ON EmpPersonalInfo.EmployeeNo = EmployeeSalaryHistory.EmployeeCode
LEFT JOIN tblEmployeeMainInfo ON tblEmployeeMainInfo.EmployeeCode=EmpPersonalInfo.EmployeeNo
LEFT JOIN EmploymentInfo on EmpPersonalInfo.EmployeeNo=EmploymentInfo.EmployeeNo
LEFT JOIN Position ON Position.PoCode = EmploymentHistory.PosCode
LEFT JOIN EmpDepartment on EmpDepartment.DeptID = EmploymentHistory.DepartmentID
where EmpPersonalInfo.EmployeeNo=902575