Forum Discussion

TCatron18's avatar
TCatron18
Copper Contributor
Dec 12, 2024

Capturing Latest Effective Date of Current Instance of Position

I am having issues trying to formulate a JOIN statement within my query that will provide the values that I am needing for all employees. Specifically, situations where an employee held the position multiple times. 

Here my current statement segment:

JOIN (SELECT R_POSITION, WORK_ASSIGNMNT, EMPLOYEE, MIN(EFFECT_DATE) AS EFFECT_DATE

FROM HR_EMP_POSITIONS

GROUP BY R_POSITION, WORK_ASSIGNMNT, EMPLOYEE)

AS EP ON EP.R_POSITION = W.POSITION

AND EP.EMPLOYEE = W.EMPLOYEE

AND EP.WORK_ASSIGNMNT = W.WORK_ASSIGNMNT

For my statement, I need to retrieve the latest EFFECT_DATE of the last time the employee held a specific position that matches their current position. 

Please find my sample document with the EP and W tables.

My current statement works for employee A; however, for employee B it is providing the 10/16/2023 date when I need it to populate the 8/26/2024 date. Any ideas on what I can do to get the data that I need? I don't have any other fields that I can use to help refine the criteria.

SQL
  • rodgerkong's avatar
    rodgerkong
    Iron Contributor
    Dec 16, 2024

    the last time the employee held a specific position that matches their current position.

    Does it means the start date that someone last time sit in the position continuous? Such as A was assigned position 7059/5 from '2022/9/19' to now without broken, so the date you need is '2022/9/19'. B was assigned position 8849/3 on '10/16/2023', but reassigned to 26041/3 on '2024/2/11', so the last time assigned to 8849/3 should be '2024/8/26'.
    Is my understanding correct?

    • TCatron18's avatar
      TCatron18
      Copper Contributor
      Dec 16, 2024

      rodgerkong 

      Correct. 

      But if B had a change to their position for 8849/3 on say '2024/11/20' we still want that '2024/8/26' date.

      • rodgerkong's avatar
        rodgerkong
        Iron Contributor
        Dec 18, 2024

        If my understanding was correct, the following steps will work. It's a little complicated.

        1. Identify the row at the starting point of each successive position/assignment.
        2. Use Recursed Query to pass the first EFFECT_DATE to the end row of each successive position/assignment.
        3. GROUPING by EMPLOYEE, S_DATE(start_date), POSTION and ASSIGNMENT to aggregated the successive position/assignment to one row. So far, we have start_date and end_date for each position assigned to each employee.
        4. GROUPING by EMPLOYEE, POSTION and ASSIGNMENT, will get final result.

        Code is here ( base on your sample data )

        CREATE TABLE #W
(
	EMPLOYEE VARCHAR(10),
	R_POSITION INT,
	WORK_ASSIGNMNT INT
);

INSERT INTO #W
VALUES
 ('A', 7059, 5)
,('B', 8849, 3);

CREATE TABLE #EP
(
	EMPLOYEE VARCHAR(10),
	R_POSITION INT,
	WORK_ASSIGNMNT INT,
	EFFECT_DATE	DATE,
	END_DATE DATE
);

INSERT INTO #EP
VALUES
('A', 7059, 5, '2022/9/19', '2022/9/24'),
('A', 7059, 5, '2022/9/25', '2023/6/17'),
('A', 7059, 5, '2023/6/18', '2024/1/13'),
('A', 7059, 5, '2024/1/14', '2024/2/10'),
('A', 7059, 5, '2024/2/11', '2024/3/8'),
('A', 7059, 5, '2024/3/9', '2024/3/9'),
('A', 7059, 5, '2024/3/10', '2024/4/20'),
('A', 7059, 5, '2024/4/21', '2099/12/31'),
('B', 23455, 3, '2022/6/27', '2022/6/29'),
('B', 22773, 3, '2022/6/30', '2022/8/26'),
('B', 22773, 3, '2022/8/27', '2022/8/27'),
('B', 22773, 3, '2022/8/28', '2022/10/22'),
('B', 22773, 3, '2022/10/23', '2022/12/27'),
('B', 22773, 3, '2022/12/28', '2023/6/17'),
('B', 22773, 3, '2023/6/18', '2023/10/15'),
('B', 8849, 3, '2023/10/16', '2024/2/10'),
('B', 26041, 3, '2024/2/11', '2024/3/9'),
('B', 26041, 3, '2024/3/10', '2024/4/20'),
('B', 26041, 3, '2024/4/21', '2024/8/25'),
('B', 8849, 3, '2024/8/26', '2099/12/31');

WITH CTE_PRE -- Prepare a dataset for next step of recurse, and filter the rows that should be focused.
AS
(
	SELECT EP.*
		--Find the rows that is the start point of each position/assignment, mark them with HAS_LAG = 0
		,IIF(DATEADD(DAY, 1, LAG(EP.END_DATE, 1, '1900-01-01') OVER (PARTITION BY EP.EMPLOYEE, EP.R_POSITION, EP.WORK_ASSIGNMNT ORDER BY EP.EFFECT_DATE))=EP.EFFECT_DATE, 1, 0) AS HAS_LAG
	FROM #EP AS EP
	INNER JOIN (SELECT R_POSITION, WORK_ASSIGNMNT, EMPLOYEE FROM #W) W 
	ON EP.R_POSITION = W.R_POSITION
		AND EP.EMPLOYEE = W.EMPLOYEE
		AND EP.WORK_ASSIGNMNT = W.WORK_ASSIGNMNT
),
CTE --Recurse dataset to pass the start date to the end rows of each position/assignment
AS
(
	--Anchor query, get start rows (HAS_LAG = 0) and start date (S_DATE)
	SELECT *
		, EFFECT_DATE AS S_DATE
	FROM CTE_PRE WHERE HAS_LAG = 0
	UNION ALL
	--Recursed query, pass start date (S_DATE) to end rows
	SELECT CN.*
		, CTE.S_DATE  FROM CTE_PRE CN 
	INNER JOIN CTE
	ON
		CN.EMPLOYEE = CTE.EMPLOYEE
		AND CN.R_POSITION = CTE.R_POSITION
		AND CN.WORK_ASSIGNMNT = CTE.WORK_ASSIGNMNT
		AND CN.EFFECT_DATE = DATEADD(DAY, 1, CTE.END_DATE)
)
--Get final result
SELECT EMPLOYEE, R_POSITION, WORK_ASSIGNMNT 
	,MAX(EFFECT_DATE) AS EFFECT_DATE
	,MAX(END_DATE) AS END_DATE 
FROM
(
-- Get EFFECT_DATE and END_DATE that sit on certain position/assignment continuous
	SELECT EMPLOYEE, R_POSITION, WORK_ASSIGNMNT, 
		S_DATE AS EFFECT_DATE          --EFFECT_DATE which passed from first row
		, MAX(END_DATE) AS END_DATE
	FROM CTE
	GROUP BY EMPLOYEE, R_POSITION, WORK_ASSIGNMNT, S_DATE
)F
GROUP BY EMPLOYEE, R_POSITION, WORK_ASSIGNMNT
;
DROP TABLE #W;
DROP TABLE #EP;

         

Resources