By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE employeeContracts (
-- DimEmployeeId int auto_increment primary key,
login text,
ValidFrom datetime,
ValidTo datetime,
JobPosition text
);
INSERT INTO employeeContracts (login, ValidFrom, ValidTo, JobPosition)
VALUES ('bcde', '2019-07-01', '2019-09-30', 'Project Manager');
INSERT INTO employeeContracts (login, ValidFrom, ValidTo, JobPosition)
VALUES ('bcde', '2020-01-09', '2020-06-16', 'Head of Center of Excellence');
INSERT INTO employeeContracts (login, ValidFrom, ValidTo, JobPosition)
VALUES ('bcde', '2020-06-17', '2020-07-31', 'Team Leader');
INSERT INTO employeeContracts (login, ValidFrom, ValidTo, JobPosition)
VALUES ('bcde', '2020-08-01', '2021-03-31', 'Team Leader');
INSERT INTO employeeContracts (login, ValidFrom, ValidTo, JobPosition)
VALUES ('bcde', '2021-04-01', '2021-06-30', 'Team Leader');
INSERT INTO employeeContracts (login, ValidFrom, ValidTo, JobPosition)
VALUES ('bcde', '2021-07-01', '2021-07-31', 'Team Leader');
INSERT INTO employeeContracts (login, ValidFrom, ValidTo, JobPosition)
VALUES ('bcde', '2021-08-01', '2021-12-31', 'Head of Center of Excellence');
INSERT INTO employeeContracts (login, ValidFrom, ValidTo, JobPosition)
VALUES ('bcde', '2022-01-01', ' 2022-05-09', 'Head of Center of Excellence');
INSERT INTO employeeContracts (login, ValidFrom, ValidTo, JobPosition)
VALUES ('bcde', '2022-02-01', '2022-05-09', 'Head of Center of Excellence');
INSERT INTO employeeContracts (login, ValidFrom, ValidTo, JobPosition)
-- Demo: Identify job position "islands"
SELECT cron.*
, SUM( IF( JobPosition = PrevPosition, 0, 1) ) OVER(
ORDER BY ValidFrom, ValidTo
) AS JobPositionGroup
FROM (
SELECT ec.*
, LAG(JobPosition, 1) OVER (
ORDER BY ValidFrom, ValidTo
) AS PrevPosition
FROM employeeContracts ec
)
cron
| login | ValidFrom | ValidTo | JobPosition | PrevPosition | JobPositionGroup |
|---|---|---|---|---|---|
| bcde | 2019-07-01 00:00:00 | 2019-09-30 00:00:00 | Project Manager | null | 1 |
| bcde | 2020-01-09 00:00:00 | 2020-06-16 00:00:00 | Head of Center of Excellence | Project Manager | 2 |
| bcde | 2020-06-17 00:00:00 | 2020-07-31 00:00:00 | Team Leader | Head of Center of Excellence | 3 |
| bcde | 2020-08-01 00:00:00 | 2021-03-31 00:00:00 | Team Leader | Team Leader | 3 |
| bcde | 2021-04-01 00:00:00 | 2021-06-30 00:00:00 | Team Leader | Team Leader | 3 |
| bcde | 2021-07-01 00:00:00 | 2021-07-31 00:00:00 | Team Leader | Team Leader | 3 |
| bcde | 2021-08-01 00:00:00 | 2021-12-31 00:00:00 | Head of Center of Excellence | Team Leader | 4 |
| bcde | 2022-01-01 00:00:00 | 2022-05-09 00:00:00 | Head of Center of Excellence | Head of Center of Excellence | 4 |
| bcde | 2022-02-01 00:00:00 | 2022-05-09 00:00:00 | Head of Center of Excellence | Head of Center of Excellence | 4 |
| bcde | 2022-05-09 00:00:00 | 2099-12-31 00:00:00 | Head of Center of Excellence | Head of Center of Excellence | 4 |
-- Get min/max date range for each island
SELECT grp.JobPositionGroup
, grp.JobPosition
, grp.Login
, MIN(grp.ValidFrom) AS ValidFrom
, MAX(grp.ValidTo) AS ValidTo
FROM (
SELECT cron.*
, SUM( IF( JobPosition = PrevPosition, 0, 1) ) OVER(
ORDER BY ValidFrom, ValidTo
) AS JobPositionGroup
FROM (
SELECT ec.*
, LAG(JobPosition, 1) OVER (
ORDER BY ValidFrom, ValidTo
) AS PrevPosition
FROM employeeContracts ec
)
cron
) grp
GROUP BY grp.JobPositionGroup
, grp.JobPosition
, grp.Login
| JobPositionGroup | JobPosition | Login | ValidFrom | ValidTo |
|---|---|---|---|---|
| 1 | Project Manager | bcde | 2019-07-01 00:00:00 | 2019-09-30 00:00:00 |
| 2 | Head of Center of Excellence | bcde | 2020-01-09 00:00:00 | 2020-06-16 00:00:00 |
| 3 | Team Leader | bcde | 2020-06-17 00:00:00 | 2021-07-31 00:00:00 |
| 4 | Head of Center of Excellence | bcde | 2021-08-01 00:00:00 | 2099-12-31 00:00:00 |