By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE EmployeeJobs (
EMPLOYEE_ID INT,
START_DATE DATE,
END_DATE DATE NULL,
JOB_TITLE VARCHAR(50)
);
INSERT INTO EmployeeJobs (EMPLOYEE_ID, START_DATE, END_DATE, JOB_TITLE)
VALUES
(1442, '2024-07-30', NULL, 'Tutor'),
(1442, '2024-07-30', NULL, 'Tutor'),
(1442, '2024-06-28', NULL, 'Instructional Specialist'),
(1442, '2024-05-01', '2024-06-27', 'Instructional Specialist'),
(1442, '2021-12-16', '2024-07-29', 'Tutor'),
(1442, '2021-12-16', NULL, 'Lead Instructor'),
(1442, '2021-12-16', '2024-07-29', 'Tutor');
7 rows affected
WITH NullEndDateFlag AS
(
SELECT DISTINCT
*,
MAX
(
CASE
WHEN END_DATE IS NULL
THEN 1
ELSE 0
END
) OVER
(
PARTITION BY EMPLOYEE_ID
) AS HAS_NULL_END_DATE
FROM EmployeeJobs
),
RowNums AS
(
SELECT
EMPLOYEE_ID,
JOB_TITLE,
ROW_NUMBER() OVER
(
PARTITION BY EMPLOYEE_ID
ORDER BY START_DATE DESC
) AS ROW_NUM
FROM NullEndDateFlag
WHERE END_DATE IS NULL
OR HAS_NULL_END_DATE = 0
)
SELECT *
FROM RowNums
PIVOT
(
MAX(JOB_TITLE)
| EMPLOYEE_ID | 1 | 2 | 3 | 4 | 5 |
|---|---|---|---|---|---|
| 1442 | Tutor | Instructional Specialist | Lead Instructor | null | null |
DELETE
FROM EmployeeJobs
WHERE END_DATE IS NULL;
4 rows affected
SELECT *
FROM EmployeeJobs;
| EMPLOYEE_ID | START_DATE | END_DATE | JOB_TITLE |
|---|---|---|---|
| 1442 | 2024-05-01 | 2024-06-27 | Instructional Specialist |
| 1442 | 2021-12-16 | 2024-07-29 | Tutor |
| 1442 | 2021-12-16 | 2024-07-29 | Tutor |
WITH NullEndDateFlag AS
(
SELECT DISTINCT
*,
MAX
(
CASE
WHEN END_DATE IS NULL
THEN 1
ELSE 0
END
) OVER
(
PARTITION BY EMPLOYEE_ID
) AS HAS_NULL_END_DATE
FROM EmployeeJobs
),
RowNums AS
(
SELECT
EMPLOYEE_ID,
JOB_TITLE,
ROW_NUMBER() OVER
(
PARTITION BY EMPLOYEE_ID
ORDER BY START_DATE DESC
) AS ROW_NUM
FROM NullEndDateFlag
WHERE END_DATE IS NULL
OR HAS_NULL_END_DATE = 0
)
SELECT *
FROM RowNums
PIVOT
(
MAX(JOB_TITLE)
| EMPLOYEE_ID | 1 | 2 | 3 | 4 | 5 |
|---|---|---|---|---|---|
| 1442 | Instructional Specialist | Tutor | null | null | null |