How do I match up what cost center an employee was in given the date of a work order when the employee changes cost centers?

I need to match up what cost center an employee was in given the date of a work order. The employee data is in a different table from the work order data. From the employee table, I have an employee id, cost center number, and cost center change effective date. From the work order table I have the work order number, work order date, and employee id of who did the work order. I can easily join the two on the employee id and tell the query to select the cost center where the cost center change effective date is greater than or equal to the work order date. That works great for all work orders that happened after the latest cost center change date. The problem that I can’t seem to solve is how to select the previous cost center for the work orders that happened earlier than the latest cost center change.

For example, I have this employee who was in cost center “3300” from the day he was hired until 12/02. On 12/03 he changed cost centers to “3100”. Then on 12/10 he changed cost centers to “3850”. I need my query results to show the cost center for work orders completed by this employee before 12/03 to show “3300”, the ones completed from 12/03-12/09 to show “3100”, and all work orders from 12/10 and on to show “3850”. Any thoughts?

I’m using Snowflake. Here is the query:

SELECT
    W.WORKORDER_NAME,
    TO_DATE(W.ASSIGNMENT_START) ASSIGNMENT_START,
    W.TECHNICIAN_BADGE_ID,
    E.COST_CENTER_ID

FROM
    RPT.T_WORKORDER W
LEFT JOIN
    HR.T_EMPLOYEE_ALL E ON W.TECHNICIAN_BADGE_ID = E.EMPLOYEE_ID
    AND W.ASSIGNMENT_START >= E.COST_CENTER_CHANGE_EFFECTIVE

WHERE 
    ASSIGNMENT_START >= '2018-12-01'
AND ASSIGNMENT_START <= '2018-12-31'
AND W.TECHNICIAN_BADGE_ID = '207361'

ORDER BY 2

This is giving me duplicate work orders with different cost centers for work orders later than each subsequent cost center change. For example, there are 3 rows for one work order completed on 12/12: one row with cost center 3100, one with 3300, and one with 3850.

Answer

You can use the LEAD operator to get data from the ‘next row’. In your case, you want to find the date that an employee stopped working for a given cost center, in addition to the date they started. In other words, for each cost center, the ‘stop’ date is the date they started working at the next center, or the ‘start’ date from the next record, if sorted by date.

SELECT
    W.WORKORDER_NAME,
    TO_DATE(W.ASSIGNMENT_START) ASSIGNMENT_START,
    W.TECHNICIAN_BADGE_ID,
    E.COST_CENTER_ID

FROM
    RPT.T_WORKORDER W
LEFT JOIN (
       SELECT e1.EMPLOYEE_ID,
              e1.COST_CENTER_ID,
              e1.COST_CENTER_CHANGE_EFFECTIVE,
              LEAD(e1.COST_CENTER_CHANGE_EFFECTIVE) 
                OVER (PARTITION BY e1.EMPLOYEE_ID 
                      ORDER BY e1.COST_CENTER_CHANGE_EFFECTIVE
                ) AS COST_CENTER_CHANGE_END
       FROM HR.T_EMPLOYEE_ALL e1
    ) E ON W.TECHNICIAN_BADGE_ID = E.EMPLOYEE_ID
    AND  W.ASSIGNMENT_START >= E.COST_CENTER_CHANGE_EFFECTIVE
    AND (W.ASSIGNMENT_START <  E.COST_CENTER_CHANGE_END OR E.COST_CENTER_CHANGE_END IS NULL)
WHERE 
    ASSIGNMENT_START >= '2018-12-01'
AND ASSIGNMENT_START <= '2018-12-31'
AND W.TECHNICIAN_BADGE_ID = '207361'

ORDER BY 2

This gets the COST_CENTER_CHANGE_EFFECTIVE of the next row as the value for COST_CENTER_CHANGE_END (unless there isn’t a next row for this employee, when it returns null). You can use that in your original join to make sure you’re only returning records for the appropriate dates.