Employees Stamp

[Luca2020]

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	IN
1036	31/12/2022 12:44	OUT

I would prefer to have a table with the entry and exit records as different columns instead.

empl_id	entry	exit	h
1036	29/12/2022 14:08	29/12/2022 22:08	8
1036	31/12/2022 07:21	31/12/2022 12:44	5

Some employees work at night so they start one day and finish the next one. 

Can someone help me? 

Thanks

[tamerj1 · Accepted solution]

@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]

Hi @Luca2020 

Do you have double punches or your data is just as clean as this sample?

[Luca2020]

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.