Forum Discussion

Luca2020's avatar
Luca2020
Helper I
3 years ago
Solved

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...
  • tamerj1's avatar
    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