select p.Departmentcode,p.Departmentname,p.Number,p.EmployeeId,p.ChineseName,p.EmployeeType,p.Technical,
DateDiff(Year,BirthDate,GetDate())-Case When DateAdd(Year,DateDiff(Year,BirthDate,GetDate()),BirthDate)>GetDate() Then 1 Else 0 END AS 'BirthDay',
'JoinAge'=
case
when datediff(mm,e.joindate,EffectiveDate)<12 and datediff(mm,e.joindate,EffectiveDate)>0 then convert(varchar(10),datediff(mm,e.joindate,EffectiveDate))+'月'
when datediff(mm,e.joindate,EffectiveDate)=0 then '1月'
when datediff(mm,e.joindate,EffectiveDate)=12 then '1年'
when datediff(mm,e.joindate,EffectiveDate)>12 and datediff(year,e.joindate,EffectiveDate)-convert(numeric(18,2),datediff(mm,e.joindate,EffectiveDate))/12=0
then convert(varchar(10),datediff(year,e.joindate,EffectiveDate))+'年'
when datediff(mm,e.joindate,EffectiveDate)>12 and datediff(mm,e.joindate,EffectiveDate)/12<>0 then
Replace(Replace(round(datediff(month,e.Joindate,EffectiveDate)/12.00,1),'00000','年'),'.年0','年')
end,
r.Salary,r.NewSalary,
'Range'= r.NewPaypackage-r.Paypackage,
r.EffectiveDate,r.Behave,r.Remark,r.Performance,r.NewPerformance
From Post p left join employeeraise r on p.Employeeid=r.Employeeid
left join Departments d on p.Departmentcode=d.Departmentcode
left join employees e on e.employeeId=p.employeeId
where convert(varchar(7),r.EffectiveDate,120)=p.YearMonth and Left(e.IncumbencyStatus,1) <> 1