Employee Work Assignment - Current Active Assignment

4min
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_deeted 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 not 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 Transformation﻿ 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
;
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.
﻿
Updated 11 Oct 2023
Did this page help you?
Employee Pay Summary - manually changed records (cloned)
Ceridian : Change Log & Notes
Docs powered by Archbee
TABLE OF CONTENTS
Create a Transformation with Post-SQL Update