Can someone help me out here please...Why am I getting wrong results? I thought this logic thoroughly but cant seem to understand where my logic went wrong..

with cte as ( select start_date, lead(start_date,1) over (order by start_date asc) as gapday FROM Projects),

GAP as ( select gapday from cte where datediff(day,start_date,gapday) > 1),

START_SET_A AS (

select min(start_date) as first from Projects UNION SELECT GAPDAY FROM GAP),

BTE as ( select END_date, lead(END_date,1) over (order by END_date asc) as endgap FROM Projects),

EGAP as ( select endgap from BTE where datediff(day,end_date,endgap) > 1),

enddatelists as ( select max(end_date) as maxi from Projects where end_date < (select min(endgap) from egap) UNION select endgap from EGAP),

ONE AS ( select first, row_number() over (order by first asc) as ranks from START_SET_A),

TWO AS ( SELECT maxi,row_number() over (order by maxi asc) as secondranks from enddatelists ),

final as (

select o.first, t.maxi, datediff(day, o.first, t.maxi) as diff from one o join two t on o.ranks = t.secondranks)

select first, maxi from final order by diff asc, first asc

WITH cte1 AS ( SELECT Start_Date, End_Date, Start_Date-ROW_NUMBER() OVER(ORDER BY Start_Date ASC)+1 AS ConsFlag FROM Projects ), cte2 AS( SELECT ConsFlag, MIN(Start_Date) AS 'Start_Date', MAX(End_Date) AS 'End_Date',COUNT(*) AS Days_Cnt FROM cte1 GROUP BY ConsFlag )

SELECT Start_Date,End_Date FROM cte2 ORDER BY Days_Cnt

with date_gap as ( select task_id, start_date, end_date, datediff(end_date, lag(end_date) over(order by end_date)) as gap from projects ), cte as ( select task_id, start_date, end_date, sum(case when gap > 1 or gap is null then 1 else 0 end) over (order by end_date) as grp

from date_gap group BY task_id, start_date, end_date ) select min(start_date) as start_date, max(end_date) as end_date from cte group by grp order by datediff(max(end_date), min(start_date)) asc, start_date asc

with a as ( SELECT task_id, start_date, end_date, lag(end_date, 1) OVER (ORDER BY start_date) AS next_end_date, DATEDIFF(end_date, lag(end_date, 1) OVER (ORDER BY start_date)) AS ddf FROM projects ),

b as( select *, case when ddf = 1 then 0 else 1 end as days_diff from a ),

c as ( SELECT *, (SUM(days_diff) OVER (ORDER BY start_date)) AS gap_group FROM b )

select min(start_date) as start_date, max(end_date) as end_date from c group by gap_group order by datediff(max(end_date), min(start_date)), start_date

SQL Server:

With cte1 as ( select Start_date, end_date, row_number() over(order by end_date) rn from projects p ) select min(start_date), max(end_date) from cte1 group by day(end_date) - rn order by datediff(day,min(start_date),max(end_date)),1 ;