Hi there,
Below is the perfect working query, shared for developers:
DECLARE @Tasks AS TABLE (
TaskID INT IDENTITY(1, 1) ,
Task NVARCHAR(128) NOT NULL ,
ForecastDate DATE NULL ,
CompletedDate DATE NULL ,
DurationInDays INT NOT NULL DEFAULT ( 1 ) ,
PRIMARY KEY ( TaskID ) )
DECLARE @TaskPredecessors AS TABLE (
PredecessorUniqueID INT IDENTITY(1, 1) ,
PredecessorID INT NULL, --refyerence of the Task to be finished before our activity can be started
TaskID INT NOT NULL, --current Task in question
PRIMARY KEY ( PredecessorUniqueID ) )
DECLARE @FinalTable AS TABLE(
ActID INT,ActName VARCHAR(50),Duration INT,Predecessor INT,StartDate DATE,EndDate DATE)
DECLARE @Test2 AS TABLE (
ID INT IDENTITY(1, 1) ,TaskID INT,Task VARCHAR(50),DurationInDays INT,PredecessorID INT, Level INT, DHPATH nvarchar(255), PRIMARY KEY ( ID ))
DECLARE @startDate date = '2019-03-23'
IF(DATENAME(DW, @startDate) = 'SUNDAY')
BEGIN
SET @startDate = DATEADD(DAY,1,@startDate)
END
INSERT INTO @Tasks ( Task, ForecastDate, DurationInDays ) VALUES
( N'Project Start', NULL, 2 ), -- 1
( N'Buy Land', NULL, 2 ), -- 2
( N'Build Walls', NULL, 2 ), -- 3
( N'Build Roof', NULL, 15 ), -- 4
( N'Build Garden', NULL, 2 ), -- 5
( N'Key Handover', NULL, 5 ), -- 6
( N'Housewarming party', NULL, 5 ) -- 7
INSERT INTO @TaskPredecessors ( PredecessorID, TaskID )
VALUES ( NULL, 1 ),
( NULL, 7 ), -- start organising the housewawrming party
( 4, 2 ), -- buy the land when the project started
( 2, 5 ), -- start building the garden when the land has bought
( 7, 4 ), -- start building the roof when the walls are standing
( 4, 6 ), -- start organising the key handover when the garden and the roof finished
( 5, 3 ) -- start building the wall when the land has bought
;with CTE as
(
SELECT
tp.TaskID,
tp.PredecessorID,
1 as Level,
cast(cast(coalesce(tp.PredecessorID,'') as nvarchar(5)) as nvarchar(255)) as DHPath
FROM
@TaskPredecessors tp
WHERE
tp.PredecessorID is null
UNION ALL
SELECT
tp.TaskID,
tp.PredecessorID,
(x.Level +1) as Level,
cast(x.DHPath + '-' + cast(coalesce(tp.PredecessorID,'') as nvarchar(5)) as nvarchar(255)) as DHPath
FROM
@TaskPredecessors tp
join
cte x on x.TaskID = tp.PredecessorID
WHERE
tp.PredecessorID is not null
)
INSERT INTO @Test2
SELECT DISTINCT C.TaskID,t.Task,t.DurationInDays, C.PredecessorID, C.Level, C.DHPath
FROM CTE C
INNER JOIN @Tasks t ON t.TaskID = C.TaskID
order by Level, DHPath
DECLARE @Counter INT
SET @Counter = 1
WHILE (@Counter <= (SELECT COUNT(*) FROM @Test2))
BEGIN
DECLARE @ActID INT,@ActName VARCHAR(50),@Duration INT,@Predecessor INT,@StartDate1 DATE,@EndDate1 DATE
SELECT @ActID = TaskID,@ActName = Task,@Duration = DurationInDays,@Predecessor = PredecessorID
FROM (SELECT ROW_NUMBER() OVER( ORDER BY (SELECT 0)) Row_Num,TaskID,Task,DurationInDays,PredecessorID FROM @Test2 ) t
WHERE t.Row_Num = @Counter
IF(@Predecessor IS NULL)
BEGIN
SET @StartDate1 = @startdate
SET @EndDate1 = DATEADD(DAY,@Duration -1,@startdate)
END
ELSE
BEGIN
SELECT @StartDate1 = EndDate FROM (SELECT ROW_NUMBER() OVER( ORDER BY (SELECT 0)) Row_Num,ActID,EndDate FROM @FinalTable WHERE ActID = @Predecessor) t
SET @StartDate1 = DATEADD(DAY,1,@startdate1)
IF DATENAME(DW, @startdate1) = 'SUNDAY'
BEGIN
SET @StartDate1 = DATEADD(DAY,1,@startdate1)
END
SET @EndDate1 = DATEADD(DAY,@Duration -1,@startdate1)
END
DECLARE @NumOfSundays INT
SELECT @NumOfSundays = DATEDIFF(WW, @StartDate1, @EndDate1)
INSERT INTO @FinalTable (ActID,ActName,Duration,Predecessor,StartDate,EndDate)
VALUES (@ActID,@ActName,@Duration,@Predecessor,@StartDate1,DATEADD(DAY,@NumOfSundays,@EndDate1))
SET @Counter = @Counter + 1
CONTINUE;
END
SELECT ActID 'Task ID', ActName 'TaskName',Duration,StartDate 'Start',EndDate 'Finish',ISNULL(CONVERT(VARCHAR(20),Predecessor),'') 'Predecessors' FROM @FinalTable
