By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Contracts (id INT, date_from DATE, date_to DATE, contract_type INT)
INSERT Contracts
VALUES
(308, '2023-01-01', '2023-09-28', 1),
(308, '2023-03-04', '2023-07-15', 2),
(308, '2023-10-01', '2024-07-31', 1),
(477, '2023-04-02', '2023-08-30', 1),
(477, '2023-06-01', '2023-12-31', 2)
;WITH CTE_AdjustedContracts AS (
SELECT
id,
date_from,
DATEADD(day, 1, date_to) AS date_to_excl, -- Adjust to excludive end-date
contract_type
FROM Contracts
),
CTE_Dates AS (
SELECT id, date_from AS Date FROM CTE_AdjustedContracts
UNION -- implicitly DISTINCT
SELECT id, date_to_excl AS Date FROM CTE_AdjustedContracts
),
CTE_DateRanges AS (
SELECT
id,
Date AS date_from,
LEAD(Date) OVER(PARTITION BY id ORDER BY date) AS date_to_excl
FROM CTE_Dates
),
CTE_SelectedContracts AS (
SELECT
AC.id,
DR.date_from,
DR.date_to_excl,
MAX(AC.contract_type) AS contract_type
FROM CTE_AdjustedContracts AC
| id | date_from | date_to | contract_type |
|---|---|---|---|
| 308 | 2023-01-01 | 2023-03-03 | 1 |
| 308 | 2023-03-04 | 2023-07-15 | 2 |
| 308 | 2023-07-16 | 2023-09-28 | 1 |
| 308 | 2023-10-01 | 2024-07-31 | 1 |
| 477 | 2023-04-02 | 2023-05-31 | 1 |
| 477 | 2023-06-01 | 2023-12-31 | 2 |