We help IT Professionals succeed at work.
Get Started

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

64 Views
Last Modified: 2018-10-15
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)

Open 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
) 

Open in new window

Linked_Task.sql
Linked_Task_Query.sql
Comment
Watch Question
EE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013
Commented:
This problem has been solved!
Unlock 1 Answer and 5 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE