如何获得员工的出席...

问题描述

我使用actatek拇指设备..他们给出了事件日志表...每日出席...但是我还没有缺席员工排......

iam using actatek thumb device.. they give eventlog table... daily attendace... but iwan't absent employee row also..

select distinct
E.USERID as [رقم الموظف],
U.User_FirstName+ ' ' + U.User_LastName as [اسم الموظف] ,
CONVERT(Date,LOCALTIMESTAMP) as [تاريخ العمل],
(select min(Convert (TIME,LOCALTIMESTAMP))
from access_event_logs As MINCE
where CAST(MINCE.LOCALTIMESTAMP as DATE)=CAST(E.LOCALTIMESTAMP As DATE) AND MINCE.EVENTID ='In' and MINCE.USERID=E.USERID) As [وقت الدخول],
(select max(Convert (TIME,LOCALTIMESTAMP))
from access_event_logs As MAXCE
where CAST(MAXCE.LOCALTIMESTAMP as DATE)=CAST(E.LOCALTIMESTAMP As DATE) AND MAXCE.EVENTID ='Out' and MAXCE.USERID=E.USERID) As [وقت الخروج]
from access_event_logs As E Inner join access_user as U on E.USERID = U.User_ID where Convert(date,LOCALTIMESTAMP) between '20/May/2014' And '20/May/2014'

推荐答案

DECLARE @Access_Logs AS TABLE (UserId NVARCHAR(10),Indate DATETIME)
DECLARE @usermaster AS TABLE (UserId NVARCHAR(10),Username NVARCHAR(MAX))

数据：

Data:

INSERT INTO @usermaster
SELECT 'U01','John' UNION
SELECT 'U02','Jessy' UNION
SELECT 'U03','Michel' UNION
SELECT 'U04','Robert' UNION
SELECT 'U05','Dolly'

INSERT INTO @Access_Logs
SELECT 'U01','2014-12-20 21:24:20.200' UNION
SELECT 'U03','2014-12-20 21:24:20.200'UNION
SELECT 'U04','2013-12-20 21:24:20.200'

主要问题是让缺席的员工参加日期：

Main Query to get the absent employee on the date:

SELECT um.* FROM @usermaster um LEFT JOIN @Access_Logs  al ON um.UserId=al.UserId and al.Indate BETWEEN '2014-12-20 00:00:00' AND '2014-12-20 23:59:59'
WHERE al.userid is null

希望这有助于.... :)

Hope this helps....:)

FROM   access_user AS U
       LEFT JOIN access_event_logs AS E
            ON  E.USERID = U.User_ID

就足够了你想要做的事情

is enough what you want to do

这篇关于如何获得员工的出席...的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！