午夜后的时间计算

Question

我有一个表，它按以下顺序存储时钟项:

UID         Clock                    Status
===         =====                    ======
R01         2013-01-01 17:00:00      Clockin
R01         2013-01-01 17:10:00      Clockin
R01         2013-01-01 23:45:00      Clockin

我目前的解决方案是使用最小/最大日期与大小写和左连接，按以下顺序排列条目

UID        Date         ClockIn    ClockOut
===        ====         =======    ========
R01        2013-01-01    17:00:00  23:45:00

当时钟进入时间超过午夜时，我该如何处理?例如,

UID         Clock                    Status
===         =====                    ======
R01         2013-01-01 17:00:00      Clockin
R01         2013-01-02 00:45:00      Clockin

如上所述将产生如下2个条目，这将导致该员工的零时间状态为7.45小时。

UID        Date          ClockIn               ClockOut
===        ====          =======               ========
R01        2013-01-01    2013-01-01 17:00:00   2013-01-01 17:00:00
R01        2013-01-02    2013-01-02 00:45:00   2013-01-02 00:45:00

提前感谢。

下面是SQL 2008R2 中使用的存储过程的代码

SELECT A.Device,A.DID, A.Name, A.ClockDate,Clockin ,ClockOut
FROM 
(
    SELECT Device,DID,Name, CONVERT(DATE, DeviceClock) 'ClockDate', 
    min(case when clock=Clock and Status ='Clock In' OR status='Clock Out' OR status='Access In' then clock  end) 'CLock In'
    FROM TABLE  
    group by Device,DID,Name, CONVERT(DATE, DeviceClock)
) as A
LEFT JOIN 
(
    SELECT Device,DID,Name, CONVERT(DATE, DeviceClock) 'ClockDate', 
max(case when clock=Clock and Status ='Clock in' or status='Clock Out' OR status='Access In' then Clock end) 'Clock Out'

    FROM TABLE 
    group by Device,DID,Name, CONVERT(DATE, DeviceClock)
) as B
ON A.DID = B.DID AND A.ClockDate = B.ClockDate

Answer 1

这里有很多你没有考虑到的。考勤系统相当复杂。例如:

• 如果你的clockkin只在午夜前几分钟，而ClockOut在早上很晚的时候呢?你把时间记到哪一天?
• 如果时间平均分配，比如晚上10点到凌晨2点，这是第一天的一个4小时轮班吗?还是第二天?还是两班两小时?
你在用UTC记录时间吗?进行本地转换?处理夏令时?(在当地时间的输出减去输入并不是经过时间的准确测量!)
• 四舍五入?减少多少?在什么情况下?
• 如何处理缺少的冲头?重复的拳吗?

整个系统已经进化到可以处理这些场景。它们通常也处理工作规则，如加班计算、工作成本计算、调度和其他事情——但它们都是从你描述的基本问题案例开始的。

你不总是与下一个打孔，或下一个特定类型，或下一个在同一日期配对。有大量的场景组合需要处理，您不能在一个简单的sql语句或存储过程中表达这一点。你可以试试——但是你会有很多边缘情况需要处理。

我会考虑购买一种已经为你处理过这些情况的产品。如果你打算自己写——不要用SQL。从SQL中获取原始数据，并将其与业务逻辑一起使用以生成处理引擎。

Answer 2

Matt说的是一个很好的一般答案，并且阅读了他提到的所有注意事项，但是如果你的情况非常简单，因为所有员工都在早上6点之后开始工作，午夜之前离开，并且在凌晨3点之前完成，那么你可以像这样很好地处理它:

SELECT Device,DID,Name, convert( DATE, dateadd(hour, -3, CONVERT(DATE, DeviceClock))) 'ClockDate', 
max(case when clock=Clock and Status ='Clock in' or status='Clock Out' OR status='Access In' then Clock end) 'Clock Out'
FROM TABLE 
group by Device,DID,Name, convert( DATE, dateadd(hour, -3, CONVERT(DATE, DeviceClock)))

中的每一个连接的选择。你有效地将一天的开始和结束时间的概念转换了3个小时。当然，如果有人在凌晨3点以后工作，或者在凌晨3点之前开始工作，这就行不通了。