Calculate Start and End Date based on condition in SQL Server

Hi EmadKhan,

Check this sample query.

SQL

DECLARE @Test AS TABLE(ActID INT,ActName VARCHAR(50),Duration INT,Predecessor INT)
INSERT INTO @Test VALUES(1,'Building Contract',2,NULL)
INSERT INTO @Test VALUES(2,'Land Survey',2,1)
INSERT INTO @Test VALUES(3,'Soil Testing',3,2)
INSERT INTO @Test VALUES(4,'Land Excavation',5,3)
INSERT INTO @Test VALUES(5,'Land Drilling',4,NULL)
 
DECLARE @Test1 AS TABLE(ActID INT,ActName VARCHAR(50),Duration INT,Predecessor INT,StartDate DATE,EndDate DATE)
 
DECLARE @Counter INT,@startdate DATE, @startdateentered DATE
SET @Counter = 1
SET @startdate = '2019-03-11'
SET @startdateentered = @startdate
 
WHILE (@Counter <= (SELECT COUNT(*) FROM @Test))
BEGIN
    DECLARE @ActID INT,@ActName VARCHAR(50),@Duration INT,@Predecessor INT,@StartDate1 DATE,@EndDate1 DATE
 
    SELECT @ActID = ActID,@ActName = ActName,@Duration = Duration,@Predecessor = Predecessor
    FROM (SELECT ROW_NUMBER() OVER( ORDER BY (SELECT 0)) Row_Num,ActID,ActName,Duration,Predecessor FROM @Test) t 
    WHERE t.Row_Num = @Counter
 
    IF(ISNULL(@Predecessor,'0') > 0)
        BEGIN
            SET @StartDate1 = @startdate
            SET @EndDate1 = DATEADD(DAY,@Duration -1,@startdate)
        END
    ELSE
        BEGIN
            SET @StartDate1 = @startdateentered
            SET @EndDate1 = DATEADD(DAY,@Duration -1,@startdate1)
        END
    DECLARE @NumOfSundays INT
    SELECT @NumOfSundays = DATEDIFF(WW, @StartDate1, @EndDate1)
 
    INSERT INTO @Test1 (ActID,ActName,Duration,Predecessor,StartDate,EndDate)
    VALUES (@ActID,@ActName,@Duration,@Predecessor,@StartDate1,DATEADD(DAY,@NumOfSundays,@EndDate1))
    SET @startdate = DATEADD(DAY,1,DATEADD(DAY,@NumOfSundays,@EndDate1))
    SET @Counter = @Counter + 1
    CONTINUE;
END
 
SELECT * FROM @Test1

Output