年假计算方法,经测试可用,如有需要请拿走.
计算代码如下(可自定义日期,仅需将getdate() 替换为指定日期即可):
s elect id 员工ID, userWorkTime 入司时间, ngongl AS 工龄,bnkynj AS 可用年假,GETDATE() as 计算时间
from
(
s elect id,userWorkTime,yybnrq,dqrq,ndrq,ngongl,jnygts,jndts,kn,
CASE
WHEN Ngongl < 1 and yybnrq > dqrq THEN 0
when Ngongl =1 and yybnrq <= dqrq and kn = 1 then ROUND( CAST ( cast(jndts as numeric(12,2))/ 365 * 5 AS NUMERIC (4, 2)), 2 )
when Ngongl =1 and yybnrq > dqrq and kn > 1 then 5
when Ngongl >1 and Ngongl <10 then 5
when Ngongl =10 and yybnrq > dqrq and kn = 10 then 5
when Ngongl =10 and yybnrq <= dqrq and kn = 10 then ROUND( CAST (cast(jnygts as numeric(12,2))/ 365 * 5 AS NUMERIC(4, 2)), 2 ) + ROUND( CAST ( cast(jndts as numeric(12,2))/ 365 * 10 AS NUMERIC (4, 2)), 2 )
when Ngongl =10 and yybnrq > dqrq and kn > 10 then 10
when Ngongl >10 and Ngongl <20 and kn >10 then 10
when Ngongl =20 and yybnrq > dqrq and kn = 20 then 10
when Ngongl =20 and yybnrq <= dqrq and kn = 20 then ROUND( CAST (cast(jnygts as numeric(12,2))/ 365 * 10 AS NUMERIC(4, 2)), 2 ) + ROUND( CAST ( cast(jndts as numeric(12,2))/ 365 * 15 AS NUMERIC (4, 2)), 2 )
when Ngongl =20 and yybnrq > dqrq and kn > 20 then 15
when Ngongl >20 and Ngongl <20 and kn >20 then 15
when Ngongl >20 then 15
else 0
end bnkynj
from
(
s elect id,userWorkTime,dqrq,ndrq,yybnrq,ngongl,kn,
(datediff(day,yybnrq,ndrq))as jndts, ---距年底天数
365-(datediff(day,yybnrq,ndrq))as jnygts --已过天数
from
(
S ELECT id, --员工ID
CONVERT(varchar(100), userWorkTime, 23)userWorkTime, --入司日期
CONVERT(varchar(100), getdate(), 23)dqrq,--当前日期
CAST (CAST (year (getdate())as varchar)+'-'+'12'+'-'+'31'AS DATE ) AS ndrq, --年底日期
CAST (CAST (year (getdate())as varchar)+'-'+SUBSTRING(convert(char(10),userWorkTime,120),6,10) AS DATE ) AS yybnrq , --入司时间引用至本年同日期
datediff(day, CONVERT(varchar(100), userWorkTime, 23),getdate())/365 AS Ngongl, -- 工龄
year(getdate()) - year(userWorkTime) kn -- 跨自然年
FROM tableA )a)b)t
加好友 
发短信
Post By：2020/1/9 16:45:00 [只看该作者]
此主题相关图片如下：年假.png