How to find timein and timeout of employee

[#1]

Here in EventID 0 represents entering data,1 represents leaving data for

here i need to get Timein i.e 7:00 am,8:45 am,8:00 am,7:20,6:45

and Timeout times 5:45 pm,5:30 pm,2:20 pm,11:45 am,3:30 pm,

please help me how to find timein,timeout timings for the data provided below

also how to find late comers by 10 min(Those who are coming after 8:10 am),over time(those who are working after 5:00pm ) office timings are 7:00 am to 5:00 pm

thanks in advanse

[gautik92]

Have a look at this

[#3]

i didn't have personal edition please share script here

thanks in advanse

[gautik92]

May b lik this

LOAD ID,

     EventID,

     EmpID,

     Timings,

     if(EventID=0,Time(Timings,'hh:mm:ss'))   as LoginTime,

      if(EventID=1,Time(Timings,'hh:mm:ss'))   as LogoutTime

FROM

[#5]

By using this i will get more login and logouts in same day i want first login time and last logout time of the day

[Anonymous]

Hi

May be like this

This will work for same day .

Min(Login) --First

Max(LogOut)--OverTime

[#7]

will u plz explain clearly

[#8 · Accepted solution]

Hi,

use the next script to load data from your excel:

DataIn:

LOAD ID,

     EventID,

     EmpID,

     trim(date(floor(Timings),'DD/MM/YYYY') )AS DateBadge,

     Timings

FROM

[<your path>\Timein_timeout.xlsx]

(ooxml, embedded labels, table is Sheet1);

DataOut:

LOAD 

     EmpID,    

     DateBadge, 

     Time(Min(Timings),'hh:mm:ss') as TimeIn,

     Time(Max(Timings),'hh:mm:ss') as TimeOut

Resident DataIn

Group by  EmpID,DateBadge;

//

Drop table DataIn;

This script produce one row for day and employee whit TimeIn and TimeOut on single row data.

So you can use the two fields of input and output as you see fit

Regards

Gennaro

[#9]

Here date badge showing nothing use

Date(floor(Timestamp#(Timings,'MM/DD/YYYY hh:mm TT'))) as DateBadge

except that everything is fine thankyou Gennaro

[#10]

Also i need to find Early(who are coming after 7:10 AM) and  overtime workers(who are working after 5:00 PM)

will any one help me