根据状态获取最新/最新记录，如果没有在日期 [英] Getting the last/latest record based on status, if not on date

问题描述

您好。我很难从表格中获取最后/最新记录。



这些是表格的结构



[表格结果]



我需要将它放在子查询中。

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

如果状态= 1，我可以区分两条记录中的最新记录，如果状态= 0表示表中的所有记录，如何我可以得到最新的记录（这取决于EmploymentHistory表的有效性，以及EffectivityDate-for EmployeeSalaryHistory）



除了获得TOP 1和ORDER BY DESC之外，是无论如何以大多数建议的方式做到这一点？



我尝试过：



我尝试了上面的代码，但我正在寻找更多的信息。



我试过这个，但我不能构建逻辑，我可以在这里操纵它？



选择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

来自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职位ON Position.PoCode = EmploymentHistory.PosCode

LEFT JOIN EmpDepartment on EmpDepartment.DeptID = EmploymentHistory.DepartmentID

其中EmpPersonalInfo.EmployeeNo = 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

推荐答案

有一种方法，您可以使用排名函数来设置序列号，然后对其进行过滤：

There is a way, you can asssign a sequence number using a ranking functions and then filter on that:

with EmploymentHistoryEx AS
(
    Select EmployeeNo, PosCode, Status, EffectivityDateFrom, EffectivityDateTo
        row_number() over(partition by EmployeeNo order by Status desc, EffectivityDateFrom desc) as HistorySeq
    from EmploymentHistory
)

您对另一个表执行相同的操作。然后在您的主查询中加入第一条记录：

You do the same for the other table. Then in your main query you join to the first record:

select 
    ...
    EmploymentHistoryEx.EffectivityDateFrom,
    EmploymentHistoryEx.EffectivityDateTo,
    EmploymentHistoryEx.Status as EmploymentStatus
    
from 
    EmpPersonalInfo 
    LEFT JOIN EmploymentHistoryEx 
        ON EmpPersonalInfo.EmployeeNo=EmploymentHistoryEx.EmployeeNo
        AND EmploymentHistoryEx.HistorySeq=1 
    ...
where EmpPersonalInfo.EmployeeNo=902575

这篇关于根据状态获取最新/最新记录，如果没有在日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！