Forum Discussion
Luca2020
3 years agoHelper I
Employees Stamp
Hi, I have a table with the daily entry and exit records for each employee. empl_id date type 1036 29/12/2022 14:08 IN 1036 29/12/2022 22:08 OUT 1036 31/12/2022 07:21 I...
- 3 years ago
Luca2020
Please try the following query.Actually I'm dealing with an attendance data that is much more complex than yours. I tried to simplify for your case. Hope it works.
AttendanceNew = VAR T1 = SELECTCOLUMNS ( FILTER ( AttendanceSource, AttendanceSource[tipologia] = "IN" ), "Emp.No.", AttendanceSource[empl_id], "@Date", DATEVALUE ( AttendanceSource[datd] ), "ClockIn", AttendanceSource[data] ) VAR T2 = SELECTCOLUMNS ( FILTER ( AttendanceSource, AttendanceSource[tipologia] = "OUT" ), "Emp.No.1", AttendanceSource[empl_id], "ClockOut", AttendanceSource[data] ) VAR T3 = GENERATE ( T1, VAR TimeIn = [ClockIn] VAR DateIn = DATEVALUE ( TimeIn ) VAR T4 = FILTER ( T2, [Emp.No.1] = [Emp.No.] && [ClockOut] > TimeIn ) VAR T5 = FILTER ( T4, DATEVALUE ( [ClockOut] ) = DateIn ) VAR T6 = FILTER ( T4, DATEVALUE ( [ClockOut] ) = DateIn + 1 ) VAR ClockOut1 = MAXX ( T5, [ClockOut] ) VAR ClockOut2 = MINX ( T6, [ClockOut] ) VAR ClockOut = COALESCE ( ClockOut1, ClockOut2 ) RETURN ROW ( "ClockOut", ClockOut ) ) RETURN T3
tamerj1
3 years agoCommunity Champion
Hi Luca2020
Do you have double punches or your data is just as clean as this sample?
- Luca20203 years agoHelper I
This is my database,
I have all the daily In and Out records for each employee,
the difficult part is when workers start one day and finish the next one, and start a new shift later on the same day.