mssql:

select min(start_date),max(end_date) From( select * , SUM(CASE WHEN status IS NOT NULL THEN 1 ELSE 0 END) OVER (ORDER BY start_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS conditional_rank from( Select task_id,start_date,end_date, case when start_date<>lag(end_date) over (order by start_date) then 'New project' when lag(end_date) over (order by start_date) is null then 'New project' end as status from projects)aa )a group by conditional_rank order by datediff(day,min(start_date),max(end_date)), min(start_date)

SELECT Start_Date, MIN(End_Date) FROM (SELECT Start_Date FROM Projects WHERE Start_Date NOT IN (SELECT End_Date FROM Projects)) AS starts, (SELECT End_Date FROM Projects WHERE End_Date NOT IN (SELECT Start_Date FROM Projects)) AS ends WHERE End_Date > Start_Date GROUP BY Start_Date ORDER BY DATEDIFF(MIN(End_Date), Start_Date) ASC, Start_Date

SELECT MIN(Start_Date) AS start_date, MAX(End_Date) AS end_date FROM ( SELECT Start_Date, End_Date, DATE_SUB(Start_Date, INTERVAL ROW_NUMBER() OVER (ORDER BY Start_Date) DAY) AS grp FROM Projects ) t GROUP BY grp ORDER BY DATEDIFF(MAX(End_Date), MIN(Start_Date)), MIN(Start_Date);

SET NOCOUNT ON; WITH DateSeq AS ( select Start_Date,End_Date,ROW_NUMBER() OVER (ORDER BY Start_Date) as DateRow FROM Projects ),

BaseDates as ( select Start_Date,End_Date, DATEADD(day,-DateRow, End_Date) as DateGroup FROM DateSeq ),

GroupedDates as ( select min(Start_Date) as MinDate, max(End_Date) as MaxDate FROM BaseDates GROUP BY DateGroup )

SELECT MinDate,MaxDate from GroupedDates

ORDER BY DATEDIFF(day,MinDate,MaxDate), MinDate ASC

select start_date, end_date_final from ( select a.*, case when (psf = 'project_starts' and pef = 'NA') then lead_ed when (psf = 'project_starts' and pef = 'project_ends') then end_date else to_date('9999-12-09') end as end_date_final from ( select start_date, end_date, psf, pef, lead(end_date) over(order by end_date) as lead_ed from ( select start_date, lag_sd, diff_sd, case when diff_sd is null or diff_sd > 1 then 'project_starts' else 'NA' end as psf , end_date, lead_ed, diff_ed, case when diff_ed is null or diff_ed > 1 then 'project_ends' else 'NA' end as pef from ( select start_date, lag_sd, (start_date-lag_sd) as diff_sd, end_date, lead_ed, (lead_ed-end_date) as diff_ed from ( select start_date, lag(start_date) over(order by start_date) as lag_sd, end_date, lead(end_date) over(order by end_date) as lead_ed from Projects ) order by start_date ) ) where (psf = 'project_starts' or pef = 'project_ends') ) a ) where end_date_final != '9999-12-09' order by (end_date_final-start_date), start_date;