具有onguard系统访问控制数据库的夜班工作人员的持续时间

Question

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

此代码给出了错误的夜间shift.can值。任何人可以帮助我修改代码，以适应白班和夜班的员工，因为他们的退出时间是第二天

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

Answer 1

总之，你有Event/s表，有EmpId，每次扫描的日期时间，没有关于TimeOut或TimeIn的信息，你想要发现，从你提供的信息有点难，我相信你有更多的数据，将有助于简化这一点，如轮班时间，任何限制，是超过小时接受和更多的边界信息。

假设你没有，那么我们需要做一些假设，例如，当员工轮班工作时，我会假设轮班是8-10小时，如果我在扫描超过14小时的扫描中看到员工回家了，我就不会关心他是夜班员工还是白班员工，如果两次连续扫描之间的间隔超过14小时，这意味着他回家了，或者换句话说，这是一个TimeOut，然后下一个条目是TimeIn。

您没有提供任何表结构或数据，因此我将忽略您的查询，并将重点放在您要解决的问题上，因此我将假设我只有一个包含所有数据的事件表，如果这对您有帮助，您可以调整此表以适应您的查询。

我将在这里创建一个内存表，并用EmpId 1填充它，这是一个白班，EmpId 2，在夜班，我将假设一些数据并进行一些计算。

Declare @Events TABLE(
EmpId int,
event_time_utc datetime
)

insert into @Events values
(1, '2014-08-21 07:38:06.000'),--first day for emp1
(1, '2014-08-21 08:39:06.000'),
(1, '2014-08-21 14:44:06.000'),
(1, '2014-08-21 15:38:06.000'),
(1, '2014-08-21 16:01:06.000'),

(1, '2014-08-22 07:40:06.000'),--second day for emp1
(1, '2014-08-22 08:50:06.000'),
(1, '2014-08-22 14:30:06.000'),
(1, '2014-08-22 15:30:06.000'),
(1, '2014-08-22 16:05:06.000'),

(1, '2014-08-23 07:38:06.000'),--3rd day for emp1
(1, '2014-08-23 08:39:06.000'),
(1, '2014-08-23 14:44:06.000'),
(1, '2014-08-23 15:38:06.000'),
(1, '2014-08-23 16:01:06.000'),

(1, '2014-08-24 07:40:06.000'),--4th day for emp1
(1, '2014-08-24 08:50:06.000'),
(1, '2014-08-24 14:30:06.000'),
(1, '2014-08-24 15:30:06.000'),
(1, '2014-08-24 16:05:06.000'),

(2, '2014-08-21 21:38:06.000'),--first day for emp2 -- night shift
(2, '2014-08-21 23:38:06.000'),
(2, '2014-08-22 01:38:06.000'),
(2, '2014-08-22 04:05:06.000'),

(2, '2014-08-22 21:38:06.000'),--first day for emp2 -- night shift
(2, '2014-08-22 23:38:06.000'),
(2, '2014-08-23 01:38:06.000'),
(2, '2014-08-23 04:05:06.000'),

(2, '2014-08-23 21:38:06.000'),--3rd day for emp2 -- night shift
(2, '2014-08-23 23:38:06.000'),
(2, '2014-08-24 01:38:06.000'),
(2, '2014-08-24 04:05:06.000'),

(2, '2014-08-24 21:38:06.000'),--4th day for emp2 -- night shift
(2, '2014-08-24 23:38:06.000'),
(2, '2014-08-25 01:38:06.000'),
(2, '2014-08-25 04:05:06.000')

现在我将使用下面的CTE来计算TimeIn和TimeOut并计算小时数。

;with cte as (
--get the next entry, and set a row number based on EmpID and time
select *
    ,LEAD(event_time_utc,1) over (partition by EmpId order by event_time_utc) nextEntry 
    ,ROW_NUMBER() over (partition by EmpId order by event_time_utc) seq
    from @Events
),cte2 as (
    --count the hours between this entry and the one after
    select *,datediff(hour,event_time_utc,nextEntry) [hours] from cte
),cte3 as (
    --if gab more then 14 or if its null, set it as time in 
    select *
        ,case when seq=1 then event_time_utc 
              when [hours]>14 then nextEntry
        else null end [TimeIn]
        from cte2
),cte4 as (
--find the seq for the Timeout 
    select *,
    Isnull(
        lead(seq) over (partition by EmpId order by event_time_utc)
        ,(select top(1) cte.seq from cte  where cte.EmpId=cte3.EmpId order by event_time_utc desc))  [TimeOutSeq]
    from cte3 where TimeIn is not null
    ),cte5 as (
    --convert the seq to timeout by joining to the same table using the TimeOutSeq to help
        select cte4.*,cte3.event_time_utc [TimeOut] from cte4
        left outer join cte3 on cte3.seq=cte4.TimeOutSeq and cte3.EmpId=cte4.EmpId
    )
    --select * from cte3
    --finally show the needed fileds only, and the hours for each employee
    select EmpId,seq,TimeIn,[TimeOut],datediff(Hour,TimeIn,[TimeOut]) [hours] from cte5 order by EmpId, TimeIn

我的数据集的结果如下：

EmpId   Seq TimeIn                  TimeOut                 hours
1       1   2014-08-21 07:38:06.000 2014-08-21 16:01:06.000 9
1       5   2014-08-22 07:40:06.000 2014-08-22 16:05:06.000 9
1       10  2014-08-23 07:38:06.000 2014-08-23 16:01:06.000 9
1       15  2014-08-24 07:40:06.000 2014-08-24 16:05:06.000 9
2       1   2014-08-21 21:38:06.000 2014-08-22 04:05:06.000 7
2       4   2014-08-22 21:38:06.000 2014-08-23 04:05:06.000 7
2       8   2014-08-23 21:38:06.000 2014-08-24 04:05:06.000 7
2       12  2014-08-24 21:38:06.000 2014-08-25 04:05:06.000 7