Get (first time in) & (last time out) in raw attendance record

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:10, 10:05, 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

Employee	Time Record	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

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.

[edit]Code block added - OriginalGriff[/edit]

Posted 9-Mar-13 4:42am

JMAM

Updated 9-Mar-13 5:23am

OriginalGriff

v2

Add a Solution

Comments

[no name] 9-Mar-13 11:29am

My first idea would to sit down and re-think what it is that you are doing. Why do allow people to clock in with clocking out? Why can they clock in multiple times? According to your sample Jorge clocks in a 10:00 but his start time is 10:05. He clocks out at 17:40 but his stop time is 17:45. How do you get that? The "best" approach is to get the clock out time and subtract the clock in time from it to get the total time.

JMAM 9-Mar-13 11:35am

Because we have a biometrics device which still accepts time in even thoug employee had already done it. Likewise when you have punch it to traditional time clock using time card then once you have decided to re punch in at the same date.time clock will just over write the card.however on the device we can't modify this and what it does to simply insert new record.

Thanks for your advice btw.

CaThey Joy Galias 23-Mar-18 7:15am

What database are you going to use?

Mohammad Imran1 30-Nov-22 4:34am

im using SQL.. and i want to add missing dates .. check my below query


select u.NAME,u.BADGENUMBER, attendance.SENSORID, attendance.CHECKDate, attendance.CheckIn ,attendance.CheckOut
,cast(DATEDIFF(n, attendance.CheckIn, attendance.checkout) / 60 as varchar) + ':' + cast(DATEDIFF(n, attendance.CheckIn, attendance.checkout) % 60 as varchar) as Minutes
from (
select temp.USERID, temp.SENSORID, temp.CHECKDate ,temp.CheckIn
,case when temp.COut = temp.CheckIn then null when temp.CheckIn is null then null else temp.COut end as CheckOut
from(
select c.USERID, c.SENSORID, convert(date, CHECKTIME) CHECKDate
,(select min(CHECKTIME) from CHECKINOUT cinout where cinout.USERID = c.USERID and cinout.SENSORID = c.SENSORID and cinout.CheckTime >= dateadd(hour, 6, convert(datetime, convert(date, c.CheckTime)))) CheckIn
,(select max(CHECKTIME) from CHECKINOUT cinout where cinout.USERID = c.USERID and cinout.SENSORID = c.SENSORID and cinout.CheckTime <= dateadd(hour, 29, convert(datetime, convert(date, c.CheckTime)))) COut


from CHECKINOUT c
group by c.USERID, c.SENSORID, convert(date, CHECKTIME), datename(DW,checktime)
)temp
) attendance
inner join userinfo u on u.USERID = attendance.USERID

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)

OriginalGriff · Accepted Answer · 2013-03-09T05:30:00

Solution 1

Exactly how you do it is difficult to say, because you don't say how you are storing your data, and where you want to do this data checking.

For example, if it is in an SQL database and stored as dates, then its is a case of a simple SQL query to return the max and min value by group:

SQL

SELECT Employee,MAX(TimeRecord), MIN(TimeRecord) FROM myTable GROUP BY Employee

If you are storing it somewhere else, then we would need to know.

[edit]Typo: "say" for "don't" - OriginalGriff[/edit]

Posted 9-Mar-13 5:30am

OriginalGriff

Updated 9-Mar-13 5:47am

v2

Comments

Mohammad Imran1 29-Nov-22 5:53am

Please tell me how can i add missing dates in below query from checkdate column


select u.NAME,u.BADGENUMBER, attendance.SENSORID, attendance.CHECKDate, attendance.CheckIn ,attendance.CheckOut
,cast(DATEDIFF(n, attendance.CheckIn, attendance.checkout) / 60 as varchar) + ':' + cast(DATEDIFF(n, attendance.CheckIn, attendance.checkout) % 60 as varchar) as Minutes
from (
    select temp.USERID, temp.SENSORID, temp.CHECKDate ,temp.CheckIn
    ,case when temp.COut = temp.CheckIn then null when temp.CheckIn is null then null else temp.COut end as CheckOut
        from(
            select c.USERID, c.SENSORID, convert(date, CHECKTIME) CHECKDate
            ,(select min(CHECKTIME) from CHECKINOUT cinout where cinout.USERID = c.USERID and cinout.SENSORID = c.SENSORID and cinout.CheckTime >= dateadd(hour, 6, convert(datetime, convert(date, c.CheckTime)))) CheckIn
            ,(select max(CHECKTIME) from CHECKINOUT cinout where cinout.USERID = c.USERID and cinout.SENSORID = c.SENSORID and cinout.CheckTime <= dateadd(hour, 29, convert(datetime, convert(date, c.CheckTime)))) COut
           
 
            from CHECKINOUT c
            group by c.USERID, c.SENSORID, convert(date, CHECKTIME), datename(DW,checktime)
        )temp
) attendance 
inner join userinfo u on u.USERID = attendance.USERID