有lenel看守的夜班员工的持续时间

Question

我正在使用Lenel Onguard和SQL server dBase为我们的员工制作考勤系统。我总结每一天的交易，让他们每一天的第一次进入和最后一次退出，并获得日期差异。以获取持续时间。但问题是夜班员工，它显示的是当天早上发生的超时，而实际退出是在第二天。所以datediff。返回错误的值。任何解决方案都非常受欢迎！

下面的代码给了我上夜班的员工错误的退出时间，但正确的入场时间，而且代码在白班员工上也很好用。根据我的代码，我有几个表，但这里最重要的表是events表: events表有一个名为devid的列，它是这里的外键和readers表上的主键。工作人员通过事件表中的阅读器19或devid 19作为外键进入

我尝试了以下代码：

SELECT DISTINCT 
    BADGE.ID, 
    UPPER(ISNULL(dbo.EMP.FIRSTNAME, ' ') + ' ' + ISNULL(dbo.EMP.LASTNAME, ' ') + ' ' + ISNULL(dbo.EMP.MIDNAME, ' '))AS NAMES, 
    A.*,
    B.TIMEOUT, 
    datediff(hour,a.[TIMEIN],b.TIMEOUT) HoursWorked 
FROM (
    SELECT empid,convert(date,event_time_utc)[Date],ltrim(right(convert(varchar(25), DATEADD(HOUR,3,CAST(min(event_time_utc)AS TIME)), 100), 7)) TIMEIN 
    FROM events INNER JOIN READER ON EVENTS.DEVID=READER.READERID INNER JOIN EVENT ON EVENTS.EVENTTYPE=EVENT.EVTYPEID AND EVENTS.EVENTID=EVENT.EVID
    WHERE  READERID=19 AND PANELID=16 AND EVDESCR='Access Granted' 
    GROUP BY empid,convert(date,event_time_utc)
) A 
JOIN 
(
    SELECT empid,convert(date,event_time_utc)[Date],ltrim(right(convert(varchar(25), DATEADD(HOUR,3,CAST(MAX(event_time_utc)AS TIME)), 100), 7)) TIMEOUT
    FROM events INNER JOIN READER ON EVENTS.DEVID=READER.READERID INNER JOIN EVENT ON EVENTS.EVENTTYPE=EVENT.EVTYPEID AND EVENTS.EVENTID=EVENT.EVID
    WHERE READERID=20 AND PANELID=16 AND EVDESCR='Access Granted' 
    GROUP BY empid,convert(date,event_time_utc)
) B on A.empid=b.empid and a.[Date]=b.[Date]
JOIN Emp on emp.id=A.EmpID 
JOIN BADGE ON BADGE.EMPID=A.EMPID
ORDER BY DATE

这是我预期的结果

EmpID        TIMEIN                    Timeout
1       2014-08-21 21:38:06.000    2014-08-22 06:00:10.000                  
2       2014-08-22 22:30:00.000    2014-08-23 06:00:10.000

下面是事件表的结构

SERIALNUM DEVID EVENTID EMPID   EVENT_TIME_UTC
1513850367  19  77      1           08/21/14 21:38:06.000
1513850372  20  8       1           08/22/14 06:00:10.000
1513850374  19  199     1           08/22/14 10:01:15.000
1513850375  20  2       1           08/22/14 10:11:19.000
1513850376  19  2       2           08/21/14 22:01:26.000
1513850377  20  54      2           08/22/14 07:01:09.000
1513850381  19  10      2           08/22/14 10:03:12.000
1513850394  20  8       2           08/22/14 10:18:17.000
1513850396  19  199     2           08/22/14 11:18:23:000

Answer 1

您似乎只想将lead()或min()作为窗口函数：

select empid, event_time_utc as timein, timeout
from (select e.*,
             min(case when e.devid <> 19 then e.event_time end) over
                 (partition by e.empid order by event_time_utc desc) as timeout
      from events e
     ) e
where devid = 19;