Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello, I have this data from a fingerprint device:
ID | Employee ID | Checktime | Checktype |
---|---|---|---|
1 | 1 | 26/10/17 08:00:00 | In |
2 | 2 | 26/10/17 08:10:00 | In |
3 | 3 | 26/10/17 08:11:00 | In |
4 | 2 | 26/10/1710:10:00 | Out |
5 | 1 | 26/10/17 12:10:00 | Out |
6 | 3 | 26/10/17 15:00:00 | Out |
7 | ... | ... | ... |
I want to know the hours worked for every employee in every day. For example I want to get:
Employee ID | Date | Hours worked |
---|---|---|
1 | 26/10/17 | 6 |
2 | 26/10/17 | 8 |
3 | ... | ... |
So I need to get from every "In" row the next "Out" row for the same Employee ID and get the difference in hours between two dates.
Any suggestion? Thanks!
Is this something you need in the script or front end? For front end, you might be able to do this
Dimension
Employee ID
Date
Expression
Interval(Only({<Checktype = {'Out'}>} Checktime) - Only({<Checktype = {'In'}>} Checktime), 'h')
In case of employee has "n" In-Out in same day, for example work from 8-12 and then from 16-20 it works?
Can you elaborate on this?
For example an employee can work on 20/10/17 from 08:00 to 12:00 (there is one In-Out) and then work from 16:00 to 20:00 (there is another In-Out) . The employee works 8 hours in 20/10/17 but in two intervals.
I'm tempted to take a crack at this, because time-oriented problems tend to be more common than we'd like, and their solutions tend to be more obnoxiously complex than one would think.
Questions:
Can a shift overlap two days? i.e they Check-In at 11:55PM and Check-Out at 12:05AM the next day?
Can there be Check-Ins and Outs that aren't paired up? Like
11:55 AM Check-In
11:56 AM Check-In
11:57 AM Check-Out
?
Can a shift overlap two days? i.e they Check-In at 11:55PM and Check-Out at 12:05AM the next day? Yes, can overlap two days.
Can there be Check-Ins and Outs that aren't paired up? No. For one employee, there are one "In" and then one "Out".
This can work if you can flags in the script for every ins and outs....
Quite new to all this myself, but wouldn't something like this work?
Temp_Data:
load * inline [
ID, EmployeeID, CheckTime, CheckType
1, 1, 26/10/17 08:00:00, In
2, 2, 26/10/17 08:10:00, In
3, 3, 26/10/17 08:11:00, In
4, 2, 26/10/17 10:10:00, Out
5, 1, 26/10/17 12:10:00, Out
6, 3, 26/10/17 15:00:00, Out
]
;
Data:
Load
*,
Time(If(Peek(EmployeeID)=EmployeeID, Time-Peek(Time), Null()), 'hh:mm') as TimeDiff;
Load *,
Time(CheckTime) as Time;
Load
ID,
EmployeeID,
Timestamp#(CheckTime, 'DD/MM/YYYY hh:mm:ss') as CheckTime,
CheckType
Resident Temp_Data
Order by EmployeeID;
Drop Table Temp_Data;
Thanks,
Simon
I have a problem, if I do Date(checktime) from script or from frontend I get same date n times.
If I have checktimes 20/10/17 08:00:00 ; 20/10/17 09:00:00
I get two dates: 20/10/17 and 20/10/17 and when I select every one it references to one checktime...