计算加班时间的新列 [英] new column to calculate overtime
本文介绍了计算加班时间的新列的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我编写并管理了这个查询,以通过 dateDiff 函数计算一个人每天工作的总时间,现在我被困在一个地方.我想计算一下,如果一个人随着时间的推移完成了比新列应该在 hh:mm 中显示 OVERITME.
I wrote and managed this query to calculate total time a person has worked per day by dateDiff function now i am stuck at one place. i want to calculate that if a person has done over time than a new column should show OVERITME in hh:mm.
在我们办公室的总工作时间为 08:00 小时,超过 8 小时被视为加班,例如如果一个人在 08:35 工作,那么一栏应该显示一个人在 00:35 工作
I n our office total working hours are 08:00 hours, greater than 8 hours are considered overtime so e.g. if a person has worked 08:35 hours than a column should show that a person has worked 00:35
查询:
with times as (
SELECT t1.EmplID
, t3.EmplName
, min(t1.RecTime) AS InTime
, max(t2.RecTime) AS [TimeOut]
, t1.RecDate AS [DateVisited]
FROM AtdRecord t1
INNER JOIN
AtdRecord t2
ON t1.EmplID = t2.EmplID
AND t1.RecDate = t2.RecDate
AND t1.RecTime < t2.RecTime
inner join
HrEmployee t3
ON t3.EmplID = t1.EmplID
group by
t1.EmplID
, t3.EmplName
, t1.RecDate
)
SELECT EmplID
,EmplName
,InTime
,[TimeOut]
,[DateVisited]
,CASE
WHEN minpart = 0
THEN CAST(hourpart AS NVARCHAR(200)) + ':00'
WHEN minpart <10
THEN CAST(hourpart AS NVARCHAR(200)) + ':0'+ CAST(minpart AS NVARCHAR(200))
ELSE CAST(hourpart AS NVARCHAR(200)) + ':' + CAST(minpart AS NVARCHAR(200))
END AS 'total time'
FROM (
SELECT EmplID
,EmplName
,InTime
,[TimeOut]
,[DateVisited]
,DATEDIFF(minute, InTime, [TimeOut])/60 AS hourpart
,DATEDIFF(minute, InTime, [TimeOut]) % 60 AS minpart
FROM times
) source
输出:
推荐答案
试试这个:
with times as (
SELECT t1.EmplID
, t3.EmplName
, min(t1.RecTime) AS InTime
, max(t2.RecTime) AS [TimeOut]
, cast(min(t1.RecTime) as datetime) AS InTimeSub
, cast(max(t2.RecTime) as datetime) AS TimeOutSub
, t1.RecDate AS [DateVisited]
FROM AtdRecord t1
INNER JOIN
AtdRecord t2
ON t1.EmplID = t2.EmplID
AND t1.RecDate = t2.RecDate
AND t1.RecTime < t2.RecTime
inner join
HrEmployee t3
ON t3.EmplID = t1.EmplID
group by
t1.EmplID
, t3.EmplName
, t1.RecDate
)
SELECT EmplID
,EmplName
,InTime
,[TimeOut]
,[DateVisited]
,convert(char(5),cast([TimeOutSub] - InTimeSub as time), 108) totaltime
,convert(char(5), case when TimeOutSub - InTimeSub >= '08:01' then
cast(TimeOutSub - dateadd(hour, 8, InTimeSub) as time) else '00:00' end, 108) as overtime
FROM times
这篇关于计算加班时间的新列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文