select DATEDIFF(DAY,TXdate,GETDATE()) AS TXday,b.dpartName,t.* from (
select uSex,uId,uRealName,uName,uDepart,
cast(CONVERT(varchar(10),substring(uCode,7,8), 120) AS date ) uCodedate1,
case when uSex =0 and uHRRole != ‘M‘ then dateadd(yy,50,cast(CONVERT(varchar(10),substring(uCode,7,8), 120) AS date ))
when uSex =1 then dateadd(yy,60,cast(CONVERT(varchar(10),substring(uCode,7,8), 120) AS date ))
when uSex =0 and uHRRole != ‘M‘ then dateadd(yy,55,cast(CONVERT(varchar(10),substring(uCode,7,8), 120) AS date ))
else GETDATE() end AS TXdate,
(DATEDIFF(DAY,cast(CONVERT(varchar(10),substring(uCode,7,8), 120) AS date ),GETDATE())) daydate
FROM UserInfo WHERE uState=1 and LEN(uCode)>15
AND ( (uSex=0 and uHRRole != ‘M‘ AND (year(getdate())- year(cast(CONVERT(varchar(10),substring(uCode,7,8), 120) AS date )) >=50))
OR (uSex=1 AND (year(getdate())- year(cast(CONVERT(varchar(10),substring(uCode,7,8), 120) AS date )) >=60))
OR (uHRRole = ‘M‘ and uSex=0 AND (year(getdate())- year(cast(CONVERT(varchar(10),substring(uCode,7,8), 120) AS date )) >=55))
)
) AS t
left join System_Dparts AS b on t.uDepart=b.dpartId
字段描述
uCode:身份证号;
uHRRole:职级。M级的女员工是55岁退休。