I solved this problem by grouping consecutive tasks based on their start dates. First, I created a CTE called NumberedTasks where I assigned a row number (ROW_NUMBER() OVER (ORDER BY Start_Date)) to each task ordered by the Start_Date. This row number gave me a way to track the original sequence of tasks.

Next, I created another CTE called GroupedTasks. In this step, I calculated a GroupID for each task by subtracting the row number from the task’s start date. This approach forces all consecutive dates (without gaps) to align with the same GroupID, because when tasks happen on consecutive days, this difference remains constant.

Finally, I grouped by this GroupID and selected the minimum start date and the maximum end date within each group. This gave me the start and end date of each project.

For sorting, I first ordered the output by the number of days the project took (DATEDIFF between start and end dates), and for projects with the same duration, I sorted them by their start date.

This approach was purely window function based, and I didn’t use any unnecessary joins or variables.

WITH NumberedTasks AS (
    SELECT
        Task_ID,
        Start_Date,
        End_Date,
        ROW_NUMBER() OVER (ORDER BY Start_Date) AS RowNum
    FROM Projects
),
GroupedTasks AS (
    SELECT
        Task_ID,
        Start_Date,
        End_Date,
        DATEADD(DAY, -ROW_NUMBER() OVER (ORDER BY Start_Date), Start_Date) AS GroupID
    FROM Projects
)
SELECT
    MIN(Start_Date) AS Start_Date,
    MAX(End_Date) AS End_Date
FROM GroupedTasks
GROUP BY GroupID
ORDER BY
    DATEDIFF(DAY, MIN(Start_Date), MAX(End_Date)),
    MIN(Start_Date);