locked
Need help determining if all predecessors for a task are completed RRS feed

  • Question

  • Please find the database scripts down below.

    This is a simple project management database. Most table names should be self explanatory. Tasks performed during the lifecycle of a project depend on project type which are defined in ProjectTypes table.

    ProjectTasks table defines the tasks that need to be performed.

    ProjectTaskSequences tells us the order in which these tasks need to be performed.

    ProjectTaskPredecessors is the table where we determine which tasks need to be completed "before" a particular task can be started.

    ProjectTaskLogs is the transactions table where we keep track of tasks as they get performed.

    I'm trying to create a SELECT statement that will give me a list of all tasks that need to be performed for a particular project. See below:

    select p.ProjectId, p.ProjectName, t.TaskId, t.TaskName, s.ExecutionSequence,
    	l.StartDate, l.EndDate, l.IsCompleted
    from Projects as p
    	inner join ProjectTypes as pt on pt.ProjectTypeId = p.ProjectTypeId
    	inner join ProjectTaskSequences as s on pt.ProjectTypeId = s.ProjectTypeId
    	inner join ProjectTasks as t on t.TaskId = s.TaskId
    	left outer join ProjectTaskLogs as l on p.ProjectId = l.ProjectId
    		and t.TaskId = l.TaskId
    where p.ProjectId = 1
    order by s.ExecutionSequence

    I want to add one more column to this SELECT. I want to call the column PredecessorsCompleted which will have a 1 or 0 value. If all the predecessors of a task are already completed, I want to see a value of 1. Otherwise, a 0.

    This column will be useful in determining whether we would allow the user to start a task or not. If the task's predecessors are not yet completed, in the application, we'll make the button grayed out so that the user cannot even start the task.

    I'd appreciate some help in finishing up this SELECT statement.

    Thank you very much. Here's the rest of SQL scripts that will create the tables and put some data in them.

    /****** Object:  Table [dbo].[Projects]    Script Date: 4/28/2014 11:26:47 AM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[Projects](
    	[ProjectId] [int] IDENTITY(1,1) NOT NULL,
    	[ProjectName] [varchar](150) NOT NULL,
    	[ProjectTypeId] [smallint] NOT NULL,
     CONSTRAINT [PK_Projects] PRIMARY KEY CLUSTERED 
    (
    	[ProjectId] 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
    /****** Object:  Table [dbo].[ProjectTaskLogs]    Script Date: 4/28/2014 11:26:47 AM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[ProjectTaskLogs](
    	[TaskId] [int] IDENTITY(1,1) NOT NULL,
    	[ProjectId] [int] NOT NULL,
    	[ProjectTaskId] [int] NOT NULL,
    	[StartDate] [datetime] NOT NULL,
    	[EndDate] [datetime] NULL,
    	[IsCompleted] [bit] NOT NULL,
     CONSTRAINT [PK_ProjectTaskLogs] PRIMARY KEY CLUSTERED 
    (
    	[TaskId] 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
    /****** Object:  Table [dbo].[ProjectTaskPredecessors]    Script Date: 4/28/2014 11:26:47 AM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[ProjectTaskPredecessors](
    	[TaskId] [int] NOT NULL,
    	[PredecessorTaskId] [int] NOT NULL,
     CONSTRAINT [PK_ProjectTaskPredecessors] PRIMARY KEY CLUSTERED 
    (
    	[TaskId] ASC,
    	[PredecessorTaskId] 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
    /****** Object:  Table [dbo].[ProjectTasks]    Script Date: 4/28/2014 11:26:47 AM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[ProjectTasks](
    	[TaskId] [int] IDENTITY(1,1) NOT NULL,
    	[TaskName] [varchar](150) NOT NULL,
     CONSTRAINT [PK_ProjectTasks] PRIMARY KEY CLUSTERED 
    (
    	[TaskId] 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
    /****** Object:  Table [dbo].[ProjectTaskSequences]    Script Date: 4/28/2014 11:26:47 AM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[ProjectTaskSequences](
    	[ProjectTypeId] [smallint] NOT NULL,
    	[TaskId] [int] NOT NULL,
    	[ExecutionSequence] [smallint] NOT NULL,
     CONSTRAINT [PK_ProjectTaskSequences] PRIMARY KEY CLUSTERED 
    (
    	[ProjectTypeId] ASC,
    	[TaskId] 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
    /****** Object:  Table [dbo].[ProjectTypes]    Script Date: 4/28/2014 11:26:47 AM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[ProjectTypes](
    	[ProjectTypeId] [smallint] IDENTITY(1,1) NOT NULL,
    	[ProjectTypeName] [varchar](50) NOT NULL,
     CONSTRAINT [PK_ProjectTypes] PRIMARY KEY CLUSTERED 
    (
    	[ProjectTypeId] 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
    SET IDENTITY_INSERT [dbo].[Projects] ON 
    
    GO
    INSERT [dbo].[Projects] ([ProjectId], [ProjectName], [ProjectTypeId]) VALUES (1, N'Single Family Home Construction for Jones Family', 1)
    GO
    SET IDENTITY_INSERT [dbo].[Projects] OFF
    GO
    SET IDENTITY_INSERT [dbo].[ProjectTaskLogs] ON 
    
    GO
    INSERT [dbo].[ProjectTaskLogs] ([TaskId], [ProjectId], [ProjectTaskId], [StartDate], [EndDate], [IsCompleted]) VALUES (1, 1, 1, CAST(0x0000A30B00E6B680 AS DateTime), CAST(0x0000A30B00FF6EA0 AS DateTime), 1)
    GO
    INSERT [dbo].[ProjectTaskLogs] ([TaskId], [ProjectId], [ProjectTaskId], [StartDate], [EndDate], [IsCompleted]) VALUES (2, 1, 2, CAST(0x0000A30D008C1360 AS DateTime), CAST(0x0000A30F011826C0 AS DateTime), 1)
    GO
    INSERT [dbo].[ProjectTaskLogs] ([TaskId], [ProjectId], [ProjectTaskId], [StartDate], [EndDate], [IsCompleted]) VALUES (3, 1, 3, CAST(0x0000A31100A4CB80 AS DateTime), NULL, 0)
    GO
    SET IDENTITY_INSERT [dbo].[ProjectTaskLogs] OFF
    GO
    INSERT [dbo].[ProjectTaskPredecessors] ([TaskId], [PredecessorTaskId]) VALUES (2, 1)
    GO
    INSERT [dbo].[ProjectTaskPredecessors] ([TaskId], [PredecessorTaskId]) VALUES (3, 2)
    GO
    INSERT [dbo].[ProjectTaskPredecessors] ([TaskId], [PredecessorTaskId]) VALUES (4, 3)
    GO
    INSERT [dbo].[ProjectTaskPredecessors] ([TaskId], [PredecessorTaskId]) VALUES (5, 4)
    GO
    INSERT [dbo].[ProjectTaskPredecessors] ([TaskId], [PredecessorTaskId]) VALUES (6, 5)
    GO
    INSERT [dbo].[ProjectTaskPredecessors] ([TaskId], [PredecessorTaskId]) VALUES (7, 6)
    GO
    SET IDENTITY_INSERT [dbo].[ProjectTasks] ON 
    
    GO
    INSERT [dbo].[ProjectTasks] ([TaskId], [TaskName]) VALUES (1, N'Project Commencement Meeting')
    GO
    INSERT [dbo].[ProjectTasks] ([TaskId], [TaskName]) VALUES (2, N'Create Project Scope')
    GO
    INSERT [dbo].[ProjectTasks] ([TaskId], [TaskName]) VALUES (3, N'Assign Team Members')
    GO
    INSERT [dbo].[ProjectTasks] ([TaskId], [TaskName]) VALUES (4, N'Survey Land')
    GO
    INSERT [dbo].[ProjectTasks] ([TaskId], [TaskName]) VALUES (5, N'Buy Materials')
    GO
    INSERT [dbo].[ProjectTasks] ([TaskId], [TaskName]) VALUES (6, N'Start Construction')
    GO
    INSERT [dbo].[ProjectTasks] ([TaskId], [TaskName]) VALUES (7, N'Final Inspection')
    GO
    SET IDENTITY_INSERT [dbo].[ProjectTasks] OFF
    GO
    INSERT [dbo].[ProjectTaskSequences] ([ProjectTypeId], [TaskId], [ExecutionSequence]) VALUES (1, 1, 1)
    GO
    INSERT [dbo].[ProjectTaskSequences] ([ProjectTypeId], [TaskId], [ExecutionSequence]) VALUES (1, 2, 2)
    GO
    INSERT [dbo].[ProjectTaskSequences] ([ProjectTypeId], [TaskId], [ExecutionSequence]) VALUES (1, 3, 3)
    GO
    INSERT [dbo].[ProjectTaskSequences] ([ProjectTypeId], [TaskId], [ExecutionSequence]) VALUES (1, 4, 4)
    GO
    INSERT [dbo].[ProjectTaskSequences] ([ProjectTypeId], [TaskId], [ExecutionSequence]) VALUES (1, 5, 5)
    GO
    INSERT [dbo].[ProjectTaskSequences] ([ProjectTypeId], [TaskId], [ExecutionSequence]) VALUES (1, 6, 6)
    GO
    INSERT [dbo].[ProjectTaskSequences] ([ProjectTypeId], [TaskId], [ExecutionSequence]) VALUES (1, 7, 7)
    GO
    SET IDENTITY_INSERT [dbo].[ProjectTypes] ON 
    
    GO
    INSERT [dbo].[ProjectTypes] ([ProjectTypeId], [ProjectTypeName]) VALUES (1, N'Project Type A')
    GO
    INSERT [dbo].[ProjectTypes] ([ProjectTypeId], [ProjectTypeName]) VALUES (2, N'Project Type B')
    GO
    SET IDENTITY_INSERT [dbo].[ProjectTypes] OFF
    GO
    ALTER TABLE [dbo].[ProjectTaskLogs] ADD  CONSTRAINT [DF_ProjectTaskLogs_IsCompleted]  DEFAULT ((0)) FOR [IsCompleted]
    GO
    ALTER TABLE [dbo].[Projects]  WITH CHECK ADD  CONSTRAINT [FK_Projects_ProjectTypes] FOREIGN KEY([ProjectTypeId])
    REFERENCES [dbo].[ProjectTypes] ([ProjectTypeId])
    GO
    ALTER TABLE [dbo].[Projects] CHECK CONSTRAINT [FK_Projects_ProjectTypes]
    GO
    ALTER TABLE [dbo].[ProjectTaskLogs]  WITH CHECK ADD  CONSTRAINT [FK_ProjectTaskLogs_Projects] FOREIGN KEY([ProjectId])
    REFERENCES [dbo].[Projects] ([ProjectId])
    GO
    ALTER TABLE [dbo].[ProjectTaskLogs] CHECK CONSTRAINT [FK_ProjectTaskLogs_Projects]
    GO
    ALTER TABLE [dbo].[ProjectTaskLogs]  WITH CHECK ADD  CONSTRAINT [FK_ProjectTaskLogs_ProjectTasks] FOREIGN KEY([ProjectTaskId])
    REFERENCES [dbo].[ProjectTasks] ([TaskId])
    GO
    ALTER TABLE [dbo].[ProjectTaskLogs] CHECK CONSTRAINT [FK_ProjectTaskLogs_ProjectTasks]
    GO
    ALTER TABLE [dbo].[ProjectTaskPredecessors]  WITH CHECK ADD  CONSTRAINT [FK_ProjectTaskPredecessors_ProjectTasks] FOREIGN KEY([TaskId])
    REFERENCES [dbo].[ProjectTasks] ([TaskId])
    GO
    ALTER TABLE [dbo].[ProjectTaskPredecessors] CHECK CONSTRAINT [FK_ProjectTaskPredecessors_ProjectTasks]
    GO
    ALTER TABLE [dbo].[ProjectTaskPredecessors]  WITH CHECK ADD  CONSTRAINT [FK_ProjectTaskPredecessors_ProjectTasks1] FOREIGN KEY([PredecessorTaskId])
    REFERENCES [dbo].[ProjectTasks] ([TaskId])
    GO
    ALTER TABLE [dbo].[ProjectTaskPredecessors] CHECK CONSTRAINT [FK_ProjectTaskPredecessors_ProjectTasks1]
    GO
    ALTER TABLE [dbo].[ProjectTaskSequences]  WITH CHECK ADD  CONSTRAINT [FK_ProjectTaskSequences_ProjectTasks] FOREIGN KEY([TaskId])
    REFERENCES [dbo].[ProjectTasks] ([TaskId])
    GO
    ALTER TABLE [dbo].[ProjectTaskSequences] CHECK CONSTRAINT [FK_ProjectTaskSequences_ProjectTasks]
    GO
    ALTER TABLE [dbo].[ProjectTaskSequences]  WITH CHECK ADD  CONSTRAINT [FK_ProjectTaskSequences_ProjectTypes] FOREIGN KEY([ProjectTypeId])
    REFERENCES [dbo].[ProjectTypes] ([ProjectTypeId])
    GO
    ALTER TABLE [dbo].[ProjectTaskSequences] CHECK CONSTRAINT [FK_ProjectTaskSequences_ProjectTypes]
    GO
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Identifier' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Projects', @level2type=N'COLUMN',@level2name=N'ProjectId'
    GO
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Self explanatory' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Projects', @level2type=N'COLUMN',@level2name=N'ProjectName'
    GO
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Identifies project type' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Projects', @level2type=N'COLUMN',@level2name=N'ProjectTypeId'
    GO
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Identifier' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ProjectTaskLogs', @level2type=N'COLUMN',@level2name=N'TaskId'
    GO
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Identifies the project' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ProjectTaskLogs', @level2type=N'COLUMN',@level2name=N'ProjectId'
    GO
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Identifies the project task' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ProjectTaskLogs', @level2type=N'COLUMN',@level2name=N'ProjectTaskId'
    GO
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Self explanatory' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ProjectTaskLogs', @level2type=N'COLUMN',@level2name=N'StartDate'
    GO
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Self explanatory' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ProjectTaskLogs', @level2type=N'COLUMN',@level2name=N'EndDate'
    GO
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Determines if the task is completed' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ProjectTaskLogs', @level2type=N'COLUMN',@level2name=N'IsCompleted'
    GO
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Identifies the task' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ProjectTaskPredecessors', @level2type=N'COLUMN',@level2name=N'TaskId'
    GO
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Identifies the task that needs to be completed before this task can start' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ProjectTaskPredecessors', @level2type=N'COLUMN',@level2name=N'PredecessorTaskId'
    GO
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Identifier' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ProjectTasks', @level2type=N'COLUMN',@level2name=N'TaskId'
    GO
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Identifies the project type' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ProjectTaskSequences', @level2type=N'COLUMN',@level2name=N'ProjectTypeId'
    GO
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Identifies the task' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ProjectTaskSequences', @level2type=N'COLUMN',@level2name=N'TaskId'
    GO
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Identifies the order in which this task will be executed' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ProjectTaskSequences', @level2type=N'COLUMN',@level2name=N'ExecutionSequence'
    GO
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Identifier' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ProjectTypes', @level2type=N'COLUMN',@level2name=N'ProjectTypeId'
    GO
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Self explanatory' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ProjectTypes', @level2type=N'COLUMN',@level2name=N'ProjectTypeName'
    GO
    


    Thanks, Sam

    Monday, April 28, 2014 3:48 PM

Answers

  • One way

    select p.ProjectId, p.ProjectName, t.TaskId, t.TaskName, s.ExecutionSequence,
    	l.StartDate, l.EndDate, l.IsCompleted, 
    	IsNull(CanBeStarted, 1) As PredecessorsCompleted 
    from Projects as p
    	inner join ProjectTypes as pt on pt.ProjectTypeId = p.ProjectTypeId
    	inner join ProjectTaskSequences as s on pt.ProjectTypeId = s.ProjectTypeId
    	inner join ProjectTasks as t on t.TaskId = s.TaskId
    	left outer join ProjectTaskLogs as l on p.ProjectId = l.ProjectId
    		and t.TaskId = l.TaskId
    	outer apply (Select 0 As CanBeStarted Where Exists(Select * 
    	    From ProjectTaskPredecessors ptp
    		left Join ProjectTaskLogs l2 On ptp.PredecessorTaskId = l2.TaskID 
    		Where ptp.TaskID = t.TaskID And IsNull(l2.IsCompleted, 0) = 0)) As pc 
    where p.ProjectId = 1
    order by s.ExecutionSequence;

    Tom

    • Proposed as answer by Fanny Liu Tuesday, May 6, 2014 8:03 AM
    • Marked as answer by Kalman Toth Monday, May 12, 2014 6:35 PM
    Monday, April 28, 2014 5:10 PM