Data Islands for Start Date and End Date Items

Below is a solution for Data Islands of Items with overlapping Start Date and End Date adapted from this article by Itzik Ben-Gan

WITH StartIslandCTE
AS
(
SELECT
ID
,Task
,StartDate
,EndDate
,IIF(StartDate BETWEEN LAG(StartDate) OVER (ORDER BY StartDate, EndDate) AND LAG(EndDate) OVER (ORDER BY StartDate, EndDate),0,1) AS StartGroup
FROM
dbo.Tasks
)

,IslandIDsCTE
AS
(
SELECT
ID
,Task
,StartDate
,EndDate
,StartGroup
,SUM(StartGroup) OVER (ORDER BY StartDate, EndDate ROWS UNBOUNDED PRECEDING) AS GroupID
FROM
StartIslandCTE
)

,ReportDataCTE
AS
(
SELECT
GroupID
,MIN(TaskApply.TaskList) AS TaskList
,MIN(StartDate) AS GroupStartDate
,MAX(EndDate) AS GroupEndDate
FROM
IslandIDsCTE
CROSS APPLY
(
SELECT
STUFF((

--query with column to make a delimited string
SELECT
',' + Task
FROM 
IslandIDsCTE tasks
WHERE
tasks.GroupID = IslandIDsCTE.GroupID
ORDER BY
tasks.Task
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

--column name of delimited string
AS TaskList
) TaskApply
GROUP BY
GroupID
)

SELECT
TaskList
,GroupStartDate
,GroupEndDate
FROM
ReportDataCTE
ORDER BY
GroupStartDate
,GroupEndDate;


Sample Data 

Result looks like this