SQL Server 2022

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
    JOIN CTE_DateRanges DR
        ON DR.id = AC.id
        AND DR.date_from < AC.date_to_excl
        AND AC.date_from < DR.date_to_excl
    GROUP BY AC.id, DR.date_from, DR.date_to_excl
),
CTE_MergedContracts AS (
    SELECT
        id,
        contract_type,
        MIN(date_from) AS date_from,
        MAX(date_to_excl) AS date_to_excl
    FROM (
        SELECT
            *,
            SUM(is_gap) OVER(PARTITION BY id, contract_type order by date_from) AS grp
        FROM (
            SELECT
                *,
                CASE WHEN date_from <> LAG(date_to_excl)
                    OVER(PARTITION BY id, contract_type order by date_from)
                THEN 1 ELSE 0 END AS is_gap
            FROM CTE_SelectedContracts
        ) G
    ) G
    GROUP BY id, contract_type, grp
)
SELECT
    id,
    date_from,
    DATEADD(day, -1, date_to_excl) AS date_to, -- Adjust back to original inclusive end-date
    contract_type
FROM CTE_MergedContracts MC
ORDER BY id, date_from

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