how to get abesent employee attedance ...

Question

1.00/5 (1 vote)

See more:

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

SQL

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'

Posted 20-May-14 0:48am

akhil.krish

Add a Solution

Comments

Thava Rajan 20-May-14 9:22am

don't know what is your absent criteria and try to use it in english alias that will give some info to us to help you

akhil.krish 22-May-14 1:57am

Hi Thava Rajan... thanks for rply...

EventLog Table like this

http://tinypic.com/view.php?pic=2nau0aq&s=8

iam using crystal report.. for daily attendace...

device only save present attendace for eventlog table.... my problem crystal report.. some body absent show the absent that day.....

http://tinypic.com/view.php?pic=jze3nr&s=8#.U3x1p_mSyYM

so need like this report.... if u know please help me.....

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

RDBurmon · Answer 1 · 2014-05-20T06:04:00

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:

SQL

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

Data:

SQL

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:

SQL

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....:)

Thava Rajan · Answer 2 · 2014-05-21T21:13:00

Solution 2

SQL

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

Posted 21-May-14 21:13pm

Thava Rajan

Comments

akhil.krish 22-May-14 5:24am

Hi, Thavan rajan.. Thanks for rply....

Same result .........

seeshil · Answer 3 · 2014-05-21T23:52:00

Solution 3

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.

Posted 21-May-14 23:52pm

seeshil

Updated 21-May-14 23:53pm

v2

Comments

akhil.krish 22-May-14 6:11am

Hi seeshil thanks for reply...
without user id data how to come.... eventlog table.. somebody absent not save the data...

so iam using right join

OutLog Query

SELECT
U.USER_ID, U.User_FirstName+ ' ' + U.User_LastName as [Employee Name] ,LOCALTIMESTAMP,EVENTID,
Convert(Date ,LOCALTIMESTAMP) as ATTDate FROM access_event_logs E Right JOIN access_user U ON E.USERID=U.User_ID
and (Convert(Date,LOCALTIMESTAMP) BETWEEN '20/may/2014' And '20/may/2014')
WHERE User_autoMatch='1' and (EVENTID ='out' or EVENTID is null) order by USER_ID

this is inlog query

SELECT distinct
U.USER_ID, U.User_FirstName+ ' ' + U.User_LastName ,LOCALTIMESTAMP,EVENTID,
Convert(Time ,LOCALTIMESTAMP) as ATTDate FROM access_event_logs E RIGHT JOIN access_user U ON E.USERID=U.User_ID
and (Convert(date,LOCALTIMESTAMP) BETWEEN '20/may/2014' And '20/may/2014')
WHERE U.User_ID is not null and User_autoMatch='1' and (EVENTID ='in' or EVENTID is null) order by USER_ID

result different showing...

inlog show 23rows between the date( 4members absent..so taltal present [in] 19

outlog show 21rows between the date ( 2 ids forget out not showing.. and 4members day total absent.. this 4 showing "null" but 2 ids not showing.. i can't understand)

Inlog:

http://tinypic.com/view.php?pic=2m5gm50&s=8

Outlog:

http://tinypic.com/view.php?pic=hs13jp&s=8#.U33M3vmSyYM

if u know pls tell me... whats is the problem.. and here one more problem also there Event[in] 22 user id times showing... but i wan't 1st IN only

how to get abesent employee attedance ...

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us