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]

I am not sure I understand.. can you share a sample to check this out?

[josecanalla]

I get error loading script: "Field CheckTime not found"

[josecanalla]

In your first response you say that I have to put two dimensions: EmployeeID and Date.

In what way can I get the "Date" dimension? Only I have timestamps.

[sunny_talwar]

You can create a date field in the script like this

Date(Floor(checktime)) as Date

[simon_minifie]

Take out the drop table.

It shouldn't happen, but sounds like the two tables are auto concatenating.

Thanks,

Smon

[cazztfplease]

I had to do something similar in one of my projects for a help ticket tracker.

In your scenario, you could just replace the 'created' field value with your 'Clock-in' value and the 'resolved' field value with your 'clock-out' value.  I would imagine similar results.

Resolution Time expression is:

Interval(interval#(ceil(($(vResolutionTime))),'s'), 'dd:hh:mm:ss') 

where the variable is defined  as:

(resolved - created) *86400