-----根据身份证计算快要退休的人员，以及离退休还有多久

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岁退休。