我正在使用我们在SQL Server中创建的模拟数据进行练习。目标是首先获取员工工作的总小时数,然后将他们的工作时间四舍五入。
规则:
- 0-15 分钟工作 - 四舍五入到 .25 小时
- 工作 16-30 分钟 - 四舍五入至 .5 小时
- 工作 31-45 分钟 - 四舍五入到 .75 小时
- 工作 46-60 分钟 - 四舍五入到下一个整小时
这是我到目前为止编写的代码:
Select datepart(week, Clockin) as Week,
Employee_ID,
TranType as payType,
sum ( CAST(DATEDIFF(Minute, ClockIn, Clockout) AS FLOAT(2))/60 ) as NetHours
From ConsolidatedHours
Group By Employee_ID, datepart(week, Clockin), TranType
Order By Employee_ID, TranType Asc
我希望我的输出看起来像这样:
+-------+--------------+-----------+------------+--------------+
| week | employee_id | payType | Nethours | RoundedHours |
+-------+--------------+-----------+------------+--------------+
| 31 | 1025 | Overtime | 6.700000 | 6.75 |
| 30 | 1025 | Standard | 30.483332 | 30.50 |
| 31 | 1025 | Standard | 20.333332 | 20.50 |
| 31 | 1077 | Overtime | 9.416666 | 9.50 |
...
我想使用案例语句来选择NetHours列,然后去掉数字部分的最后一部分,如果它落在一定范围内,则将25,50,75或00附加到NetHours列的左半部分(如果有意义(。这是我的选择声明:
Case
When Cast( Substring( Cast( NetHours as nvarchar) , Patindex('%[^0-9]%' , NetHours ) , 2) as int) Between 0 and 25
Then Concat( substring(NetHours , 1 , Patindex('%[^0-9]%' , NetHours) ) , '25')
When Cast( Substring( Cast( NetHours as nvarchar) , Patindex('%[^0-9]%' , NetHours) , 2) as int) Between 26 and 50
Then Concat( substring(NetHours , 1 , Patindex('%[^0-9]%' , NetHours) ) , '50')
When Cast( Substring( Cast( NetHours as nvarchar) , Patindex('%[^0-9]%' ,NetHours) , 2) as int) Between 51 and 75
Then Concat( substring(NetHours , 1 , Patindex('%[^0-9]%' , NetHours) ) , '75')
When Cast( Substring( Cast( NetHours as nvarchar) , Patindex('%[^0-9]%' , NetHours) , 2) as int) Between 76 and 99
Then Concat( substring(NetHours , 1 , Patindex('%[^0-9]%' , NetHours) ) , '00')
End As RoundedHours
你可以为此使用算术:
select sum(datediff(second, ClockIn, Clockout) / (60.0 * 60)) as net_hours,
convert(numeric(10, 2),
floor( (0.24 + sum(datediff(second, ClockIn, Clockout)) / (60.0 * 60) * 4
) / 4
)