SQL Recursive Query: Define Overall Start & Due Dates of a Group by its Children

Hello Experts,

I have a table (def & data attached), called Linked_Tasks, which has tasks with start and due dates (Start_Date, Due_Date), and those tasks belong to groups (defined by Task_Parent_ID). A group has a field Is_Group set to 'Y'. I need to write a query which sets the start date and due date of each group by the min start date and max due date, respectively, from the tasks in that group. I have a recursive query started, attached, but could use help in the best way to write that query to set the overall start/due dates of groups of tasks.

Any ideas?

Thank you!
Steve

USE [WSAProject]
GO
/****** Object:  Table [dbo].[Linked_Task]    Script Date: 10/12/2018 3:20:13 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Linked_Task](
	[Task_ID] [int] NOT NULL,
	[Task_Parent_ID] [int] NULL,
	[Linked_Task_ID] [int] NULL,
	[Linked_Task_Type] [char](2) NULL,
	[Is_Group] [char](1) NULL,
	[Task_Name] [varchar](200) NULL,
	[Start_Date] [date] NULL,
	[Due_Date] [date] NULL,
 CONSTRAINT [PK_Linked_Task] PRIMARY KEY CLUSTERED 
(
	[Task_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[Linked_Task] ([Task_ID], [Task_Parent_ID], [Linked_Task_ID], [Linked_Task_Type], [Is_Group], [Task_Name], [Start_Date], [Due_Date]) VALUES (1, 8, NULL, NULL, N'N', N'Task A', CAST(N'2018-10-01' AS Date), CAST(N'2018-10-10' AS Date))
INSERT [dbo].[Linked_Task] ([Task_ID], [Task_Parent_ID], [Linked_Task_ID], [Linked_Task_Type], [Is_Group], [Task_Name], [Start_Date], [Due_Date]) VALUES (2, 8, 1, N'SS', N'N', N'Task B', CAST(N'2018-10-05' AS Date), CAST(N'2018-10-16' AS Date))
INSERT [dbo].[Linked_Task] ([Task_ID], [Task_Parent_ID], [Linked_Task_ID], [Linked_Task_Type], [Is_Group], [Task_Name], [Start_Date], [Due_Date]) VALUES (3, 8, 2, N'FS', N'N', N'Task C', CAST(N'2018-10-16' AS Date), CAST(N'2018-10-19' AS Date))
INSERT [dbo].[Linked_Task] ([Task_ID], [Task_Parent_ID], [Linked_Task_ID], [Linked_Task_Type], [Is_Group], [Task_Name], [Start_Date], [Due_Date]) VALUES (4, 9, NULL, NULL, N'N', N'Task D', CAST(N'2018-10-15' AS Date), CAST(N'2018-10-18' AS Date))
INSERT [dbo].[Linked_Task] ([Task_ID], [Task_Parent_ID], [Linked_Task_ID], [Linked_Task_Type], [Is_Group], [Task_Name], [Start_Date], [Due_Date]) VALUES (5, 9, 4, N'FS', N'N', N'Task E', CAST(N'2018-10-18' AS Date), CAST(N'2018-10-25' AS Date))
INSERT [dbo].[Linked_Task] ([Task_ID], [Task_Parent_ID], [Linked_Task_ID], [Linked_Task_Type], [Is_Group], [Task_Name], [Start_Date], [Due_Date]) VALUES (6, 9, 5, N'SS', N'N', N'Task F', CAST(N'2018-10-18' AS Date), CAST(N'2018-10-25' AS Date))
INSERT [dbo].[Linked_Task] ([Task_ID], [Task_Parent_ID], [Linked_Task_ID], [Linked_Task_Type], [Is_Group], [Task_Name], [Start_Date], [Due_Date]) VALUES (7, 9, 6, N'FS', N'N', N'Task G', CAST(N'2018-10-25' AS Date), CAST(N'2018-11-01' AS Date))
INSERT [dbo].[Linked_Task] ([Task_ID], [Task_Parent_ID], [Linked_Task_ID], [Linked_Task_Type], [Is_Group], [Task_Name], [Start_Date], [Due_Date]) VALUES (8, NULL, NULL, NULL, N'Y', N'GROUP 1', NULL, NULL)
INSERT [dbo].[Linked_Task] ([Task_ID], [Task_Parent_ID], [Linked_Task_ID], [Linked_Task_Type], [Is_Group], [Task_Name], [Start_Date], [Due_Date]) VALUES (9, NULL, NULL, NULL, N'Y', N'GROUP 2', NULL, NULL)

Select allOpen in new window

USE [WSAProject]
GO
/****** Object:  StoredProcedure [dbo].[wsa_ProjectTask_s]    Script Date: 10/12/2018 3:20:35 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



WITH Task_CTE AS (

	SELECT  
	CAST(right('000' + cast(row_number()over(partition by Task.Task_Parent_ID order by Task.Order_By, Task.Due_Date) as varchar(250)),3) as varchar(250)) 
	as Sort, 
	1 as TaskLevel,
	lt.Task_ID,
	lt.Task_Parent_ID,
	lt.Task_Name,
	lt.Linked_Task_ID,
	lt.Linked_Task_Type,
	lt.Is_Group,
	lt.[Start_Date],
	lt.Due_Date
	From Linked_Task lt
	WHERE  (lt.Task_Parent_ID IS NULL Or lt.Task_Parent_ID = 0)

UNION ALL

	SELECT 
	CAST(Sort +  + '/' + right('000' + cast(row_number()over(partition by t.Task_Parent_ID order by t.Order_By, t.Due_Date) as varchar(250)),3) as varchar(250)) 
	as Sort, 
	t_cte.TaskLevel + 1,
	lt.Task_ID,
	lt.Task_Parent_ID,
	lt.Task_Name,
	lt.Linked_Task_ID,
	lt.Linked_Task_Type,
	lt.Is_Group,
	lt.[Start_Date],
	lt.Due_Date
FROM Linked_Task lt
	INNER JOIN Task_CTE as t_cte ON lt.Task_Parent_ID = t_cte.Task_ID
) 

Select allOpen in new window

Linked_Task.sql
Linked_Task_Query.sql