Excluding weekends (using DATEDIFF function to identify employees first 3 days as training)

John-Peddle
John-Peddle Contributor
in Magic ETL

A search for Exclude Weekends and Exclude Weekends using DateDiff Function returned a few results, none of which would seem to work for what we're trying to do so checking to see if we can get some help on this!

 

We're trying to add, in the dataflow itself, as part of the output dataset, a transform that would add a new column by  calculating:

1) an employees first 3 SUCCESSIVE dates of work that we would generally consider to be TRAINING, i.e. Employee Start Date - Date when difference is <= 2

2) exclude weekends since there is no training on weekends, meaning an employee who started mid-week, say on a Thursday, would have trained on Thursday, Friday, and the following Monday

3) the goal is to identify those days as YES in a new column (EMPLOYEE TRAINING), and use that column as a quick filter to exclude training days for billing reconciliation with external partners who provide staffing solutions for our call center.

 

This is what we have in the dataflow to determine JUST the first 3 days of employment (seems to work for some, not for others, kind of hit or miss, but obviously not at all for those who have training before and after a weekend):

CASE WHEN DATEDIFF(`Date`,`Start Date`) <=2 AND `User Company` = 'XXX' THEN 'Yes' ELSE 'No' END as `Employee Training`

 

Screen Shot 2019-08-22 at 12.09.32 PM.png

 

 

 

 

 

 

** The Date field is derived from a DateTime field of the date their shift started, and Start Date is pulled from an Employee Roster (Domo Webform) that shows an employees hire date (column is to the far right, not visible).

 

Any ideas on how to exclude weekends, preferably using a transform within the dataflow, though we're open to a beast mode at the card level if necessary? Thanks in adance for your time & assistance!  Smiley Happy

 

John

Best Answers

  • cwolman
    cwolman Contributor
    Answer ✓

    Hi John,

     

    I was able to accomplish this request by using two transforms.  The first transform calculates the training_end_date by adding more days when required to account for Saturday and Sunday.training_date transform.PNG

     

     

    The second excludes the dates between the start_date and training_end_date if they are a Saturday or Sunday.exclude_weekend transform.PNG

     


    -----------------
    Chris
  • cwolman
    cwolman Contributor
    Answer ✓

    Hi John,

     

    You will need to update the sql to use the shift start date column instead of the start date column and we can use the date() function to convert the datetime to a date.

     

    training_date transform1.PNGexclude_weekend transform2.PNG


    -----------------
    Chris
  • cwolman
    cwolman Contributor
    Answer ✓

    No worries John.  If i understand correctly the problem is that the User Start Date column is not reflecting the correct date because employees are not actually starting on the date provided by management.  I am assuming that you are looking for an automatic way to determine the "true" start date of an employee based on the T&A system (I will assume it is a T&A system).

     

    This could be tricky because I do not know the specifics of the system you are using but I would assume there is a unique ID for each employee so you can join the 2 datasets together on that column.  I would first query the T&A data to find the 1st starting date of each employee by ID.  Something similar to:

     

    select `employee_id`, min(date(`Actual Start DateTime`)) as actual_start_date from `T&A Data` group by `employee_id`

     

    The transform could then be joined to the employee roster webform data by employee ID and you would have the correct starting date without manual entry in the web form and would result in having the actual start date on each row for each day for each employee.

     

    Hopefully this makes sense and is not more confusing.

     


    -----------------
    Chris

Answers

  • cwolman
    cwolman Contributor
    Answer ✓

    Hi John,

     

    I was able to accomplish this request by using two transforms.  The first transform calculates the training_end_date by adding more days when required to account for Saturday and Sunday.training_date transform.PNG

     

     

    The second excludes the dates between the start_date and training_end_date if they are a Saturday or Sunday.exclude_weekend transform.PNG

     


    -----------------
    Chris
  • John-Peddle
    John-Peddle Contributor

    Hey Chris (@cwolman), sorry for the late response but thanks so much for your assistance on this!

     

    The solution you provided worked but we've now been advised by leadership that an employees start date won't always be same as their first date of work, i.e. scheduled or actual work hours (that's a timedate field, i.e. May 21, 2019 8:30:00 AM, not date only as the manually added start date is).

     

    Since that datetime field is dynamic, based on their schedule and always changing, wondering if you'd have any insight on how we'd use the FIRST scheduled date of work (Shift Start DateTime) as opposed to the employee start date (User Start Date) that was initially submitted in this request?

     

    EXAMPLE (screenshot): Joe gets hired with a start date of 6/17/2019 but his first scheduled date of work/training isn't until 6/18/2019. Since the first 3 scheduled days would be considered training, even if they're absent which seems to happen more frequently than expected, we'd want 6/18, 6/19, & 6/20 to be considered training, not 6/17, 6/18, & 6/19.

     

    Screen Shot 2019-08-26 at 12.11.46 PM.png

     

     

     

     

     

     

     

     

    Appreciate any insight you'd be able to provide. Thanks!

  • cwolman
    cwolman Contributor
    Answer ✓

    Hi John,

     

    You will need to update the sql to use the shift start date column instead of the start date column and we can use the date() function to convert the datetime to a date.

     

    training_date transform1.PNGexclude_weekend transform2.PNG


    -----------------
    Chris
  • John-Peddle
    John-Peddle Contributor

    Chris (@cwolman), apologies but believe I left out a vital piece of information that would keep the solution you provided from working in our instance, very sorry about that.

     

    We're starting the dataflow with TWO (2) input datasets: 

    • The first is a manually updated employee roster, and the User Start Date is based on what's provided to us by management, and input as new employees are added to the Domo Webform roster.
    • The second is imported via API from a system that agents login/logout, where the DateTime fields are based on Scheduled Hours (Shift Start DateTime & Shift End DateTime) and Actual Hours worked (Actual Start DateTime & Actual End DateTime) so they'll NEVER be duplicated for an employee.

     

    The DATE field you see is derived, directly from the Shift Start DateTime field, in the API import dataset as date only; that was done as part of the original calculation for the number of training days before realizing weekends were keeping us from being able to accurately calculate it.

     

    So, when applying the transform updates you suggested we end up getting different Training End Dates since no Shift Start DateTime field is the same, i.e. they'll match the Date field and be consecutive, never repeating themselves.

     

    Screen Shot 2019-08-26 at 2.17.32 PM.png

     

     

     

     

     

     

     

     

    I guess what we'd need to do is identify the very first Shift Start DateTime field, by employee, and then base the training transform off that specific date only; not sure if that can even be done? 

     

    Again, sorry the specifics weren't clarified as needed before, my mistake and apologies for the time you spent without all of the information needed to assess the ask properly...

     

    John

  • cwolman
    cwolman Contributor
    Answer ✓

    No worries John.  If i understand correctly the problem is that the User Start Date column is not reflecting the correct date because employees are not actually starting on the date provided by management.  I am assuming that you are looking for an automatic way to determine the "true" start date of an employee based on the T&A system (I will assume it is a T&A system).

     

    This could be tricky because I do not know the specifics of the system you are using but I would assume there is a unique ID for each employee so you can join the 2 datasets together on that column.  I would first query the T&A data to find the 1st starting date of each employee by ID.  Something similar to:

     

    select `employee_id`, min(date(`Actual Start DateTime`)) as actual_start_date from `T&A Data` group by `employee_id`

     

    The transform could then be joined to the employee roster webform data by employee ID and you would have the correct starting date without manual entry in the web form and would result in having the actual start date on each row for each day for each employee.

     

    Hopefully this makes sense and is not more confusing.

     


    -----------------
    Chris
  • John-Peddle
    John-Peddle Contributor

    Chris (@cwolman), that did it! Added the additional transform first in the dataflow, did a couple of joins and now we're able to show the Date (actual date), Start Date/Hire Date (from Employee Roster), User Training Start Date, and User Training End Date for external employees. All calculations appear to spot on, taking into account weekends as well as those who had their first shift AFTER the Start Date/Hire Date. 

     

    Screen Shot 2019-08-28 at 11.17.06 AM.png

     

    Thanks so much for all your help on this, it's greatly appreciated; most importantly I was able to learn a lot from your expertise, plus it saved us a lot of time by not having to escalate this to a development team! Smiley Happy

Categories