Identify two consecutive dates and get hours difference between it

[josecanalla]

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!

[sunny_talwar]

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')

[josecanalla]

In case of employee has "n" In-Out in same day, for example work from 8-12 and then from 16-20 it works?

[sunny_talwar]

Can you elaborate on this?

[josecanalla]

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.

[JustinDallas]

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

?

[josecanalla]

  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".

[sunny_talwar]

This can work if you can flags in the script for every ins and outs....

[simon_minifie]

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

[josecanalla]

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