Best approach to get (first time in) & (last time out) for each employees attendance record on each day

Question

0.00/5 (No votes)

See more:

Good Day,

I am so confused which is the best approach to get employee's (first time in) & (last time out) each day when they have login to our biometrics clock system.

For instance if "Jorge" has a schedule of 8am - 5pm on March 9, 2013
and he logged many times and has a record attendance of: time in at 10:05, 10:10, 10:15 and Time out at : 5:00. 5:30, 5:45

System should only gets "Jorge's" attendance for today like:

Time in: March 9, 2013 - 10:05 am & Time out: March 9, 2013 - 05:45 pm

Emp_name Emp_time Emp_type
Jorge 3/9/2013 10:00 In
Jorge 3/9/2013 10:10 In
Jorge 3/9/2013 17:00 Out
Jorge 3/9/2013 17:30 Out
Mike 3/9/2013 17:40 Out
Jorge 3/9/2013 10:02 In
Jorge 3/9/2013 17:00 Out
Jorge 3/9/2013 17:00 Out
Jorge 3/9/2013 17:00 Out
Jorge 3/9/2013 10:14 In
Mike 3/9/2013 10:00 In
Mike 3/9/2013 10:10 In
Jorge 3/9/2013 17:00 Out
Mike 3/9/2013 17:30 Out
Jorge 3/9/2013 17:40 Out
Mike 3/9/2013 10:02 In
Mike 3/9/2013 17:00 Out
Mike 3/9/2013 17:00 Out
Mike 3/9/2013 17:00 Out
Jorge 3/9/2013 10:14 In

table name: Raw_Record
Col1 = Emp_name
Col2 = Emp_time
Col3 = Emp_type

Honestly i haven't tried any code because i am still thinking what is the best approach to get (first time in) & (last time out) for each employees attendance record on each day.

An idea from anyone would be very much helpful. thanks in advance.

Posted 9-Mar-13 4:43am

JMAM

Updated 9-Mar-13 5:36am

v3

Add a Solution

1 solution

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)

Mike Meinz · Accepted Answer · 2013-03-09T05:12:00

Solution 1

Example for Microsoft SQL Server:

SQL

select distinct
CE.Emp_Name,
(select min(Emp_Time) from Raw_Record As MINCE where MINCE.Emp_Type ='In' and MINCE.Emp_Name=CE.Emp_Name AND CONVERT(varchar(8),MINCE.EMP_TIME,112)=CONVERT(varchar(8),CE.Emp_Time,112)) As MinTime,
(select max(Emp_Time) from Raw_Record As MAXCE where MAXCE.Emp_Type ='Out' and MAXCE.Emp_Name=CE.Emp_name AND CONVERT(varchar(8),MAXCE.EMP_TIME,112)=CONVERT(varchar(8),CE.Emp_Time,112)) As MaxTime
from Raw_Record As CE

MINCE (Minimum ClockEvents) is table_alias for Raw_Record table used in selection of Minimum Time.
MAXCE (Maximum ClockEvents) is table_alias for Raw_Record table used in selection of Maximum Time.
CE (ClockEvents) is table_alias for Raw_Record table used in main SELECT statement.

Posted 9-Mar-13 5:12am

Mike Meinz

Updated 2-Apr-14 2:38am

v5

Comments

JMAM 9-Mar-13 11:27am

Hi Mike,

Can you clarify this code assuming we have a database
table name: Raw_Record
Col1 = Emp_name
Col2 = Emp_time
Col3 = Emp_type

Ce.name = Employee Name
Eventtime = Time Record

What is ClockEvents Mince?

Thank you for quick response btw.

Mike Meinz 9-Mar-13 13:26pm

Updated Solution 1 to use your column and table names.

JMAM 11-Mar-13 11:03am

Thank you mike. It works perfectly.

Mike Meinz 11-Mar-13 12:09pm

That's terrific!

JMAM 6-Apr-13 12:58pm

Hi Mike,

I have a little problem with this current code, Please have a look at this for further details, Your answer are one of the closest have so far.

http://www.codeproject.com/Questions/573570/SelectplusColumnplusforpluseachplusday

Mike Meinz 6-Apr-13 19:14pm

Sorry. Late in looking at my emails. I was coding all day today. I answered your other question. A fairly simple solution, I think!

JMAM 6-Apr-13 22:24pm

Great, that's all i wanted for so long can't figure to manage as short code as you have provided me.

JMAM 15-Apr-13 13:25pm

Hi Mike,

Another questions relevant to my previous questions, hope you can answer.

http://www.codeproject.com/Questions/578114/DeterminplusTimeplusDifferenceplusonplusaplusholid

Mike Meinz 15-Apr-13 14:36pm

Submitted a solution and then updated it to handle both holiday and non-holiday days.

JMAM 15-Apr-13 22:54pm

Thanks, I fell a sleep while coding. i am checking it now.

JMAM 16-Apr-13 2:10am

Can you transfer it to a MySQL version?

Mike Meinz 16-Apr-13 7:00am

Like I said, I do not have MySQL. So the answer would be no.

akhil.krish 2-Apr-14 6:57am

query execute
select distinct
CE.USERID,
(select min(TIMESTAMPS) from access_event_logs As MINCE where MINCE.EVENTID ='In' and MINCE.USERID=CE.USERID) As MinTime,
(select max(TIMESTAMPS) from access_event_logs As MAXCE where MAXCE.EVENTID ='Out' and MAXCE.USERID=CE.USERID) As MaxTime
from access_event_logs As CE

after output like this

001 2014-01-15 08:20:17.000 2014-04-01 11:21:02.000
002 2014-01-15 08:06:19.000 2014-04-02 10:06:46.000
003 2014-01-14 11:00:31.000 2014-03-19 11:20:03.000
004 2014-01-15 07:35:20.000 2014-04-01 11:34:38.000
005 2014-01-15 08:28:32.000 2014-04-01 11:27:15.000
006 2014-01-15 08:03:44.000 2014-04-01 11:18:02.000
007 2014-01-15 09:18:56.000 2014-04-02 10:24:57.000
008 2014-01-16 08:23:06.000 2014-04-02 10:23:56.000
009 2014-01-15 08:53:19.000 2014-04-01 11:18:32.000
010 2014-01-15 08:32:07.000 2014-04-01 11:20:02.000
011 2014-01-15 07:56:49.000 2014-04-02 10:24:51.000
012 2014-01-15 08:28:44.000 2014-04-01 11:22:09.000
013 2014-01-15 08:22:01.000 2014-04-02 10:22:25.000
014 2014-01-15 08:15:36.000 2014-04-02 09:00:09.000

but i want same day IN and OUT, somebody forget the out that value null...

how to solve this problem

Mike Meinz 2-Apr-14 8:29am

I used Improve Solution to update the SELECT statement example so that it checks for the date in the column SELECTs for MINCE and MAXCE.

akhil.krish 2-Apr-14 16:06pm

select distinct CE.USERID,
(select min(TIMESTAMPS) from access_event_logs As MINCE where CAST(MINCE.TIMESTAMPS as DATE)=CAST(CE.TIMESTAMPS As DATE) AND MINCE.EVENTID ='In' and MINCE.USERID=CE.USERID) As MinTime,
(select max(TIMESTAMPS) from access_event_logs As MAXCE where CAST(MAXCE.TIMESTAMPS as DATE)=CAST(CE.TIMESTAMPS As DATE) AND MAXCE.EVENTID ='Out' and MAXCE.USERID=CE.USERID) As MaxTime
from access_event_logs As CE
where LOCALTIMESTAMP between '03/03/2014' And '03/06/2014' and USERID= '019'

this query working fine but i want 2more columns.. 1) total time calculate between the time and and current date

please help me..

akhil.krish 2-Apr-14 16:14pm

HI Mike Meinz,

this query working fine

select distinct CE.USERID,
(select min(TIMESTAMPS) from access_event_logs As MINCE where CAST(MINCE.TIMESTAMPS as DATE)=CAST(CE.TIMESTAMPS As DATE) AND MINCE.EVENTID ='In' and MINCE.USERID=CE.USERID) As MinTime,
(select max(TIMESTAMPS) from access_event_logs As MAXCE where CAST(MAXCE.TIMESTAMPS as DATE)=CAST(CE.TIMESTAMPS As DATE) AND MAXCE.EVENTID ='Out' and MAXCE.USERID=CE.USERID) As MaxTime
from access_event_logs As CE
where LOCALTIMESTAMP between '03/03/2014' And '03/06/2014' and USERID= '019'

but i need 2more columns also 1) between the date Total time calculate 2)current date

please help me....