Asuming that it is one day for each task, the solution proposed of substracting row numbers to date works perfectly. I'll give you one that works when we asume tasks can take more than a day: SELECT min(Table2.Start_Date) Start_Date, max(Table2.End_Date) End_Date FROM(SELECT Table1., SUM(Validador) over (partition by 1 order by Start_Date) Project FROM (SELECT Projects., lag(End_Date) over (partition by 1 order by End_Date) Next_Date, CASE WHEN Start_Date = lag(End_Date) over (partition by 1 order by End_Date) THEN 0 ELSE 1 END Validador FROM Projects) Table1) Table2 GROUP BY Table2.Project ORDER BY count(Table2.Project) ASC, min(Table2.Start_Date) ASC; We could use with blocks, but i was a little lazy to do it :P.

Check out the complete video solution here with detailed explanation - https://www.youtube.com/watch?v=JFud6Ui4dOU

WITH CTE AS( SELECT *, COALESCE(LAG(End_Date,1) OVER(ORDER BY Start_Date),Start_Date) AS PED FROM Projects ), CTE1 AS( SELECT Start_Date, End_Date, SUM(CASE WHEN Start_Date = PED THEN 0 ELSE 1 END) OVER(ORDER BY Start_Date) AS grouping FROM CTE ), CTE2 AS( SELECT
*, COUNT(grouping) OVER(PARTITION BY grouping) AS cnt FROM CTE1 ) SELECT MIN(Start_Date) As Proj_Start_Date, MAX(End_Date) AS Proj_End_Date FROM CTE2 GROUP BY grouping,cnt ORDER BY cnt ASC, Proj_Start_Date

This is the easiest solution. We create project groups by subtracting the row number from the start date which essentially give us same date for the same project. And then we group by project grp.

Task_ID Start_Date End_Date RowNumber Group Identifier (Start_Date - RowNumber) 1 2023-01-01 2023-01-02 1 2023-01-01 - 1 = 2022-12-31 2 2023-01-02 2023-01-03 2 2023-01-02 - 2 = 2022-12-31 3 2023-01-03 2023-01-04 3 2023-01-03 - 3 = 2022-12-31

WITH ProjectGroups AS ( SELECT *, DATE_SUB(Start_Date, INTERVAL ROW_NUMBER() OVER(ORDER BY Start_Date) DAY) AS grp FROM Projects ), GroupedProjects AS ( SELECT MIN(Start_Date) AS project_start, MAX(End_Date) AS project_end, DATEDIFF(MAX(End_Date), MIN(Start_Date)) AS project_days FROM ProjectGroups GROUP BY grp ) SELECT project_start, project_end FROM GroupedProjects ORDER BY project_days ASC, project_start;