关于处于借调状态的人员的查询,系统中目前没有现成的地方可以查得准确,而如果要借助查询语句的话, T_HR_personloan 借调经历表作为基础表是最准确的,然后关联其他的信息表,基本可以获取到借调人员信息的全貌,以满足客户关于处于借调中的人员的各种查询和统计需要。
以下的查询语句供参考。
select e.FName_L2 姓名,e.FNumber 员工编号,
(select fname_l2 from T_ORG_ADMIN where fid =b1.FCompanyID) 目标任职公司,
(select fname_l2 from T_ORG_ADMIN where fid =b1.FDepartmentID) 目标任职部门,
(select fname_l2 from T_ORG_ADMIN where fid =b2.FCompanyID) 源任职公司,
(select fname_l2 from T_ORG_ADMIN where fid =b2.FDepartmentID) 源任职部门,
a.floandate 借调日期, h.fname_l2 用工关系状态, g.FEFFDT 用工关系状态生效日期,
g.FLEFFDT 用工关系状态失效日期,
isnull(a.fbackeffectdate,isnull(f.FLeftDate,'2199-12-31')) 借调调回日期
from T_HR_personloan a left join T_ORG_Admin b1 on a.FinadminID=b1.FID
left join t_hr_emporgrelation bb on a.FPersonID=bb.FPersonID and bb.FLEFFDT='2199-12-31' and bb.FAssignType=1
left join T_ORG_Admin b2 on bb.FAdminOrgID=b2.FID
left join T_ORG_OrgFunction c1 on b1.FOrgFunctionID=c1.FID
left join T_ORG_OrgFunction c2 on b2.FOrgFunctionID=c2.FID
left join T_BD_PERSON e on a.FPersonID=e.FID
left join t_hr_personposition f on a.FPersonID=f.FPersonID
left join t_hr_emplaborrelationhis g on a.FPersonID=g.FPersonID
and g.FLEFFDT>=a.FLoanDate and g.FEFFDT<=isnull(a.fbackeffectdate,isnull(f.FLeftDate,'2199-12-31'))
left join T_HR_BDEmployeeType h on g.FLaborRelationStateID=h.FID
where f.FEnterDate<=a.FLoanDate
推荐阅读