如何根据小数点后的第一位小数按照自定义规则取整

 

 

SELECT al.NUHours,al.NUHours/2920,ji.JoinGroupDate,datediff(day,ji.JoinGroupDate,GETDATE()) AS 天数,
cast(cast(al.NUHours*datediff(day,ji.JoinGroupDate,GETDATE())as decimal(38,2))/2920 AS DECIMAL(38,1)),floor((cast(cast(al.NUHours*datediff(day,ji.JoinGroupDate,GETDATE())as decimal(38,2))/2920 AS DECIMAL(38,1)))*2)/2
FROM DMH.dbo.vwEmpBasicInfoAll AS ebi
LEFT JOIN DMH.dbo.AnnualLeave AS al ON al.EmpNo =ebi.EmployeeNo
LEFT JOIN dmh.dbo.JobInfo AS ji on ji.EmpNo=ebi.EmployeeNo WHERE ebi.[Status]='Probation' AND Year='2018'
AND ebi.EmployeeNo='BGOSH00506'

 

这边的floor((cast(cast(al.NUHours*datediff(day,ji.JoinGroupDate,GETDATE())as decimal(38,2))/2920 AS DECIMAL(38,1)))*2)/2

用了年假乘以一年的小时数再算试用期到现在可以享受的天数，再根据0-0.5用0 ，0.5到1用0.5的规则取整。

 

综上所述

如果是

0.0 取整后为 0，
0.0<@x<=0.5 取整后为 0，
0.5<@x<=1  取整后为 0.5，
1<@x<=1.5  取整后为 1

就用 floor(x*2)/2

如果是

0.0 取整后为 0，
0.0<@x<=0.5 取整后为 0.5，
0.5<@x<=1  取整后为 1，
1<@x<=1.5  取整后为 1.5

就要ceiling(x*2)/2