如何获得员工的出席... [英] how to get abesent employee attedance ...

问题描述

我使用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'

推荐答案

我不知道是什么你的数据库结构是，但我会在非常简单的考试中解释你的解决方案ple



详细信息：

我正在使用xyz设备及时记录员工。我的数据库有两个表Access_Logs和usermaster

Access_Logs表存储来自设备的关于userid和indatetime的信息

usermaster表存储所有用户信息（员工信息）

现在我想要一份2014-12-20缺席的员工名单



请参阅以下查询并修改您的查询以获得结果



数据库结构：

I don't know what's ur database structure is, But I will explain you the solution in very simple example

Details:
I am using xyz device to record employees in-time. My database has two tables Access_Logs and usermaster
Access_Logs table stores information from device about userid and indatetime
usermaster table stores all user information(Employee information)
Now I want a list of employee who are absent on 2014-12-20

See below query and modify your query to get the result

Database Structure:

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

是的，从查询部分就足够但是你有在InTime和OutTime为空或空的情况下，将Crystal Report中的自定义文本设置为不存在。

Yes That is enough from query part but you have give custom Text in your Crystal Report as Absent where InTime and OutTime is Empty or Null.

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