`with all_date as ( select Start_Date as date_ from Projects union all select End_Date as date_ from Projects ) , table_data_point as ( select date_, count(date_) as number_dup from all_date group by 1 having count(date_) = 1 ) , table_add_key_point as ( select date_, case when date_ in (select Start_Date as date_ from Projects) then 'Start' else 'End' end as Segment from table_data_point ) , start_table as ( select date_, row_number() over(order by date_) as rk from table_add_key_point where 1=1 and Segment = 'Start' ) , end_table as ( select date_, row_number() over(order by date_) as rk from table_add_key_point where 1=1 and Segment = 'End' ) select start_table.date_ as start_date, end_table.date_ as end_date from start_table join end_table using(rk) order by datediff(end_table.date_, start_table.date_) asc, 1 asc

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);

MySQL:

WITH table AS (SELECT *, End_Date - ROW_NUMBER() OVER (ORDER BY End_Date) AS gr FROM Projects)

SELECT MIN(Start_Date) AS Start_Date, MAX(End_Date) AS End_Date FROM table1 GROUP BY gr ORDER BY COUNT(*), MIN(Start_Date) ;

MySQL query :

with cte as ( Select start_date,end_date from projects order by start_date asc )

,cte2 as ( Select *, end_date - row_number() over (order by start_date) as id from cte )

,cte3 as ( Select min(start_date) as start_datemin,max(end_Date) as end_datemax, max(end_Date)-min(start_date) as delay from cte2 group by id )

Select start_datemin,end_datemax from cte3 order by delay asc, start_datemin asc

with projects_group as ( select start_date, end_date, end_date - row_number() over(order by end_date) pj_gr from Projects ) select min(start_date) pj_start_date, max(end_date) pj_end_date from projects_group group by pj_gr order by max(end_date) - min(start_date), pj_start_date;