表中的加班列

Question

办公时间:上午10时至下午18时

我已经写了这个查询来显示加班，如果一个人已经工作超过'8小时‘，例如，如果他已经工作了'08:44小时’，那么加班显示00:44，但现在我不想要这个。

我不想根据TOTAL WORK TIME来计算它。我想如果人已经工作超过时钟时间下午18:00，那么加班应该显示。

例如，如果人员从上午10:50工作到下午18:33，则加班列应显示00:33。只有当一个人在晚上18:00以上工作时，才应该计算加班。

注：事实上，我们的办公室政策是为员工支付加班津贴，所以他们计算加班的时间只限于下午18点以上，即使人们在下午17点到达办公室并工作到晚上18点54分，也会获得54分钟的加班工资，除此之外，他没有工作的时间会导致扣减工资，但仍然适用于加班津贴。

WITH Times AS
(   SELECT  emp.EmplID, 
            emp.EmplName,
            InTime = MIN(atd.RecTime),
            OutTime = MAX(atd.RecTime),
            TimeWorked = DATEDIFF(MINUTE, MIN(atd.RecTime), MAX(atd.RecTime)),
            OverTime = DATEDIFF(MINUTE, MIN(atd.RecTime), MAX(atd.RecTime)) - 480,
            [DateVisited] = atd.RecDate
    FROM    AtdRecord atd 
            INNER JOIN HrEmployee emp 
                ON atd.EmplID = emp.EmplID 
    GROUP BY emp.EmplID, emp.EmplName, atd.RecDate
    HAVING COUNT(atd.RecTime) > 1
)
SELECT  t.EmplID,
        t.EmplName,
        t.InTime,
        t.OutTime,
        t.DateVisited,    
        TimeWorked = CONVERT(CHAR(5), DATEADD(MINUTE, t.TimeWorked, 0), 8),
        OverTime = CASE WHEN t.OverTime < 0 THEN '-' ELSE '' END +
                                CONVERT(CHAR(5), DATEADD(MINUTE, ABS(t.OverTime), 0), 8)
FROM Times t

Answer 1

这就是你想要的逻辑吗？

DECLARE @EndTimeA   TIME = '18:00',
        @EndTimeB   TIME = '18:05',
        @EndTimeC   TIME = '19:00',
        @EndTimeD   TIME = '17:55'


SELECT   OvertimeMM_A   =   CASE    WHEN @EndTimeA > '18:00' THEN DATEDIFF(MINUTE, '18:00', @EndTimeA)
                                ELSE 0
                            END
        ,OvertimeMM_B   =   CASE    WHEN @EndTimeB > '18:00' THEN DATEDIFF(MINUTE, '18:00', @EndTimeB)
                                ELSE 0
                            END
        ,OvertimeMM_C   =   CASE    WHEN @EndTimeC > '18:00' THEN DATEDIFF(MINUTE, '18:00', @EndTimeC)
                                ELSE 0
                            END
        ,OvertimeMM_D   =   CASE    WHEN @EndTimeD > '18:00' THEN DATEDIFF(MINUTE, '18:00', @EndTimeD)
                                ELSE 0
                            END

表示为TIME；

DECLARE @EndTimeA   TIME = '18:00',
        @EndTimeB   TIME = '18:05',
        @EndTimeC   TIME = '19:00',
        @EndTimeD   TIME = '17:55'


SELECT   CONVERT(CHAR(5), DATEADD(MINUTE, OvertimeMM_A, '00:00'), 8)
        ,CONVERT(CHAR(5), DATEADD(MINUTE, OvertimeMM_B, '00:00'), 8)
        ,CONVERT(CHAR(5), DATEADD(MINUTE, OvertimeMM_C, '00:00'), 8)
        ,CONVERT(CHAR(5), DATEADD(MINUTE, OvertimeMM_D, '00:00'), 8)
FROM 
(
    SELECT   OvertimeMM_A   =   CASE    WHEN @EndTimeA > '18:00' THEN DATEDIFF(MINUTE, '18:00', @EndTimeA)
                                    ELSE 0
                                END
            ,OvertimeMM_B   =   CASE    WHEN @EndTimeB > '18:00' THEN DATEDIFF(MINUTE, '18:00', @EndTimeB)
                                    ELSE 0
                                END
            ,OvertimeMM_C   =   CASE    WHEN @EndTimeC > '18:00' THEN DATEDIFF(MINUTE, '18:00', @EndTimeC)
                                    ELSE 0
                                END
            ,OvertimeMM_D   =   CASE    WHEN @EndTimeD > '18:00' THEN DATEDIFF(MINUTE, '18:00', @EndTimeD)
                                    ELSE 0
                                END
)DT