Try to search your question here, if you can't find : Ask Any Question Now ?

Time Duration for staff on a night shift with onguard system accesscontrol database

HomeCategory: stackoverflowTime Duration for staff on a night shift with onguard system accesscontrol database
Avatarbhawya asked 1 week ago

I am using the Lenel Onguard with SQL server dBase to make the time attendance system for our employees.
I summarize each day’s transaction to make their first entry and last exit for each day and get the datediff. to get the time duration. But the problem is with night shift employees, it is showing the time out which happened in the morning of the same day where actual exit is on the next day. so the datediff. returning with wrong value. Any solutions are most welcomed!

This code gives me the wrong value for night shift.can anyone help me out to modify the code to fit staff on day shift and those on night shift in which their exit time is on next day

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

Results

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
1 Answers
Best Answer
AvatarAmit answered 1 week ago
Your Answer

12 + 5 =

Popular Tags

WP Facebook Auto Publish Powered By : XYZScripts.com