Forum Discussion

Luca2020's avatar
Luca2020
Icon for Helper I rankHelper I
3 years ago
Solved

Employees Stamp

Hi, 

I have a table with the daily entry and exit records for each employee. 

empl_id       datetype
103629/12/2022 14:08IN
103629/12/2022 22:08OUT
103631/12/2022 07:21IN
103631/12/2022 12:44OUT

 

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

empl_id          entry        exit h
103629/12/2022 14:0829/12/2022 22:08 8
103631/12/2022 07:2131/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's avatar
    tamerj1
    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

     

3 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    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's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Hi Luca2020 

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

    • Luca2020's avatar
      Luca2020
      Icon for Helper I rankHelper I
      3 years ago

      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.