Employee Work Assignment

current active assignment employees may have multiple work assignments in dayforce as a possible way your dayforce system is configured for your organization as assignments for an employee can change those changes are tracked by dlh io however, dlh io, does not update the is active or is deleted value in the raw landing zone to designate which assignment is the active assignment for the employee while in dayforce an administrator can see a previous assignment for an employee has ended there is no actual active status, only date of assignment completion this logic is the same when it lands in your target destination and because the business logic from one dayforce customer to another can be different based on their industry or the general business practice of people management from one industry to another, dlh io does not assume an active or de active assignment status rather, it is left to the customer if this situation occurs, and determining the active assignment per employee is required, to account for this situation a sql transformations docid 5zjgrvbhtywqw8 olioh0 needs to be created in datalakehouse io in order to mark the dlh is active flag to true or false depending on the business logic requirements dlh io support team can assist with this exercise for your implementation or it can be accomplished with some mid level sql skill by following the general advise below create a transformation with post sql update in order to work with identifying an active employee assignment these basic steps act as a general exercise to query the data and then determine if the business logic will meet the business requirement if not, additional tuning or assistance from support will be require using the sql logic here, determine if this provides the current active work assignment status of your employees if so you can move to the steps below this and understand how to create a pre sql update in the system sql select , row number() over (partition by employee xref order by last modified timestamp desc, dlh finish ts desc) rn from datalakehouse raw\ our ceridian dayforce employee work assignment where effective end is null qualify rn=1 ; login into datalakehouse io and click on transformations select your target connection select the option for sql (or post sql) enter a sql (post sql) update statement such as this one below and then save the transformation note please test this post sql logic before adding it to the transformation you must make sure that it works via a sql editor and verified the results before simply adding it to the transformation update datalakehouse raw\ our ceridian dayforce employee work assignment set dlh is active = true, dlh is deleted = false where employee xref in ( select employee xref, row number() over (partition by employee xref order by last modified timestamp desc, dlh finish ts desc) rn from datalakehouse raw\ our ceridian dayforce employee work assignment where effective end is null qualify rn=1 ) ; after you've entered the post sql logic complete the remainder of the required fields in the form save the transformation form next time the sync bridge runs and completes a load for the employee work assignment table your transformation and the update statement will run prior to loading the data into your target schema deleted work assignments ceridian dayforce allows certain users to deleted employee work assignments while the deleted records remain visible in the work audit screen in dayforce, they are no longer provided to the dayforce api this situation can cause records that have been replicated to your target connection to not be marked as is deleted which will lead to incorrect data to be used in downstream applications and/or data models employee work assignments should never be deleted in dayforce since the best practice is to set an end date on the work assignment if records are deleted, datalakehouse io still has you covered! use the following process in datalakehouse io to land the data and when the work assignment is deleted, dlh io will only mark the record for analytics as deleted and no longer active but will keep the record in your target source create a transformation with post sql update in order to work with identifying an employee work assignment that has been deleted these basic steps act as a general exercise to query the data and then determine if the business logic will meet the business requirement if not, additional tuning or assistance from support will be require if you've been running the ceridian dayforce connector in dlh io for awhile and you're unsure how many records have been deleted in ceridian dayforce, run a historical re sync docid\ dteqsg5 ax5b axwo2o2y once the sync bridge has completed the historical sync, use the sql logic here, to determine how many records should be marked as is deleted = true if there are records that should be marked is deleted = true, move to the steps below to create a pre sql update in datalakehouse io sql query to show only the deleted records in employee work assignment which will eventually be marked is deleted = true select e from datalakehouse raw\ our ceridian dayforce employee work assignment e left join datalakehouse raw tmp our ceridian dayforce employee work assignment t on e id = t id where t id is null; login into datalakehouse io and click on transformations select your target connection select the option for sql (or post sql) enter a sql (post sql) update statement such as this one below and then save the transformation note please test this post sql logic before adding it to the transformation you must make sure that it works via a sql editor and verified the results before simply adding it to the transformation update datalakehouse raw\ our ceridian dayforce employee work assignment set dlh is deleted = true, dlh is active = false, dlh finish ts = current timestamp where "id" in ( select ewa "id" from datalakehouse raw\ our ceridian dayforce employee work assignment ewa left join datalakehouse raw tmp our ceridian dayforce employee work assignment tmp on ewa "id" = tmp "id" where tmp "id" is null ); after you've entered the post sql logic complete the remainder of the required fields in the form save the transformation form set the employee entity to execute a historical re sync docid\ dteqsg5 ax5b axwo2o2y next time the sync bridge runs and completes a historical load for the employee work assignment table your transformation and the update statement will run prior to loading the data into your target schema