Forum Discussion

TiresAS's avatar
TiresAS
Regular Visitor
2 years ago

Please help - at wit's end.

I'll try to keep this short and concise - I've been working on a project to identify worked periods where employees are not taking a meal break. Our company is adamant that we want employees to be taking all of their breaks and getting rest during their shift, but we have a lot of employees who will skip clocking out for lunch for the extra paid time. We have a data lake semantic model connection using DataHub directly from our HR platform (UKG).

Meal breaks (captured in the table 'Activity by Labor Category' column [Activity Name - Level 1] as "Meal Break Unpaid" and "MEALBREAKUNPAID") are not activities that employees clock in to but are 'created' by UKG based on periods of time where the employee is clocked out based on their worked location and the assigned meal break period (ie. an employee who works in our CA location is assigned a 45 minute break, so UKG looks for a period around 45 minutes where they've clocked out and assigns it as "Meal Break Unpaid").

In the 'Timecard Punches' table, I have various data available which I have been unsuccessful in manipulating to get what I need. I have tried using the [Shift Start Date Time] and [Shift End Date Time] (their scheduled start and end time for their shift) as outer bounds and then summing the minutes where they are clocked out between those two points (the length of time between clock out where [Type] is "OUT PUNCH" which occurred after the Shift Start and [Type] "IN PUNCH" which occurred before the Shift End). Technically the employee can clock in or out before or after each Shift Start or Shift End, and ultimately is somewhat irrelevant, but I believe created issues with my calculation because each shift essentially has two IN PUNCH events and two OUT PUNCH events and the shifts are not tied/indexed necessarily. An employee can be scheduled for 9/4/2024 4:00PM start and have an end of 9/5/2024 4:00 AM but these are two separate rows of data.

Can anyone help me with figuring out which direction I need to go? I'm becoming increasingly defeated and have not been able to figure this out. I've resorted to trying to get ChatGPT to help and it has not produced viable results either. I had one iteration which returned a few correct results, but overall missed the mark. If I've missed providing any relevant data, please let me know.

Need Help

2 Replies

  • kleigh's avatar
    kleigh
    Icon for Responsive Resident rankResponsive Resident
    2 years ago

    I think showing a sample of your tables may help understand the problem. If I'm understanding right:

    • You have a table of clock in and clock out events
    • You have a table of employee schedule start and end times
    • The database does not relate these directly
    • The clock times are not necessarily inside the schedule times
    • You need to group all the clock events together that best fit a particular schedule time


    There are various tricks that may work, but this depends on how the schedules interect with one another. My first thought would be that clock times are going to be close to the schedule time, so if schedules have nice even gaps between them then creating a serial number from the date/time would let all events group together. This can be misaligned from actual dates.

    It might be possible to use a trick of referring to the previous row. Make sure the table is sorted by employee and event time. If you have a complete set of events, then you could create some running fields:

    • Partition index number
    • Date/Time Session started
    • Hours "On" in current session
    • Hours "Off" in current session

    By examining the pairs of events, you can find periods of clocked in and out. If you find a clock in event NN hours after a clock out event (or change in employee ID), assume this is a new session, so increment the index and reset your counters. You'd then need something to extract only the final rows - for example group by index, calculate the max event time, join this back to the table and filter when event time = the max you found. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi TiresAS ,

     

    I suggest you to share a easy sample file with us. And you can show a screenshot with the result you want.

    This will make it easier for us to find the solution.

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.