WITH base AS ( SELECT p.Task_ID , p.Start_Date , p.End_date , p.logic , SUM(p.logic) OVER(ORDER BY p.Start_Date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS sum_preceding FROM( SELECT Task_ID , Start_Date , End_date , IFNULL(CASE WHEN DATEDIFF(Start_date, LAG(End_date, 1) OVER(ORDER BY Start_Date)) = 0 THEN 0 ELSE 1 END,0) logic

FROM Projects ) p ORDER BY p.Start_Date )

, result AS ( SELECT sum_preceding , MIN(Start_Date) AS Start_Date , MAX(End_date) AS End_date FROM base GROUP BY sum_preceding )

SELECT Start_Date , End_date FROM result ORDER BY DATEDIFF(End_date, Start_Date) ASC, Start_Date

WITH base AS ( SELECT p.Task_ID , p.Start_Date , p.End_date , p.logic , SUM(p.logic) OVER(ORDER BY p.Start_Date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS sum_preceding FROM( SELECT Task_ID , Start_Date , End_date , IFNULL(CASE WHEN DATEDIFF(Start_date, LAG(End_date, 1) OVER(ORDER BY Start_Date)) = 0 THEN 0 ELSE 1 END,0) logic

FROM Projects ) p ORDER BY p.Start_Date )

, result AS ( SELECT sum_preceding , MIN(Start_Date) AS Start_Date , MAX(End_date) AS End_date FROM base GROUP BY sum_preceding )

SELECT Start_Date , End_date FROM result ORDER BY DATEDIFF(End_date, Start_Date) ASC, Start_Date

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