How to calculcate next date based on days from other table excluding weekend and holidays

[velmurugan]

Hi, I have 3 tables. T1 contains rows of tasks and expected time to complete the task. T2 contains the effort required for each task. T3 contains the holidays. The goal is if someone defines the start date in first row of T1, based on effort required in T2 and holidays in T3, the next rows should auto calculdated and updated with expected date to finish the task.

I tried following https://community.atlassian.com/t5/Confluence-questions/Ho-do-I-calculate-date-plus-days-without-weekends-in-Table/qaq-p/1562965 and https://community.atlassian.com/t5/Confluence-questions/Table-Transformer-Working-Days-Except-Public-Holidays/qaq-p/1626563. But unable to get the desired result. 

I could just make it to update with start date + days with below script. I am new to SQL and table transformer macro. 

SET @CFD = (SELECT T1.'ETA' FROM T1 WHERE T1.'Milestone'="CLMv2 Code Freeze");

SELECT *,
CASE
WHEN T1.'ETA' IS NULL AND T1.'Milestone' IN (SELECT T2.'Milestone' FROM T2)
THEN DATEADD(day, (SELECT T2.'days' FROM T2 WHERE T1.'Milestone'=T2.'Milestone'), @CFD)
ELSE T1.'ETA'
END
AS ETA

 

Thanks in advance for your help.

[Alexey Mikhaylov _Stiltsoft_]

Hi @velmurugan

As your case is not rather trivial, at the moment I can suggest the following option for the Table Transformer macro:

SET @CFD = (SELECT T1.'Start Date' FROM T1 LIMIT 1);
SET @SIZE = (SELECT COUNT(*) FROM T2);
SET @CNT = 0;CREATE TABLE TX;
INSERT INTO TX SELECT *, ROWNUM(*) AS '#' FROM T2;WHILE @CNT < @SIZE
BEGIN
SET @CNT = @CNT + 1;
SET @days = (SELECT FIRST('effort') FROM TX WHERE '#' = @CNT LIMIT 1);
SET @daysNext = @days + 2 * ((@days / 5)::integer) +
(IF(@CFD::Date->getDay() + @days % 5 >= 6, 2,0));
SET @days = @days - 1;
SET @daysETA = @days + 2 * ((@days / 5)::integer) +
(IF(@CFD::Date->getDay() + @days % 5 >= 6, 2,0));
SET @NEXT_CFD = DATEADD(day, @daysNext, @CFD);
SET @ETA_CFD = DATEADD(day, @daysETA, @CFD);
UPDATE TX SET TX.'ETA' = @ETA_CFD, TX.'Start Date' = @CFD WHERE '#' = @CNT;
SET @CFD = @NEXT_CFD;
END;SELECT T1.*,
COALESCE(TX.'Start Date', T1.'Start Date') AS 'Start Date',
FORMATDATE(TX.'ETA') AS 'ETA' FROM T1
LEFT JOIN TX ON T1.'Milestone' = TX.'Milestone'

We noticed that your “effort” seems to mean the raw number of days. And your “days” seems to be its cumulative count. So, the query helps you to get rid of this manual count.
The query also considers weekends and prolongates periods accordingly.Unfortunately, as for now we don't have a working solution for "holidays" - I'll return to this thread in case of any updates.

Best regards,

Alexey

[Alexey Mikhaylov _Stiltsoft_]

Hi @velmurugan,

Here is our update regarding your third table with holidays. Please adjust your SQL query as following:

SET @CFD = (SELECT T1.'Start Date' FROM T1 LIMIT 1);
SET @SIZE = (SELECT COUNT(*) FROM T2);
SET @CNT = 0;
SET @holidaysCount = 0;
SET @prevHolidaysCount = 0;

CREATE TABLE TX;
INSERT INTO TX SELECT *, ROWNUM(*) AS '#' FROM T2;

WHILE @CNT < @SIZE
BEGIN
SET @CNT = @CNT + 1;
SET @days = (SELECT FIRST('effort') FROM TX WHERE '#' = @CNT LIMIT 1) - 1 + @holidaysCount;
SET @daysETA = @days + 2 * ((@days / 5)::integer) +
(IF(@CFD::Date->getDay() + @days % 5 >= 6, 2,0));
SET @ETA_CFD = DATEADD(day, @daysETA, @CFD);
SET @prevHolidaysCount = @holidaysCount;
SET @holidaysCount = (SELECT COUNT(*) FROM T3 WHERE T3.'Holidays' >= @CFD
AND T3.'Holidays' <= @ETA_CFD);
SET @holidaysCount = IF(@holidaysCount = @prevHolidaysCount, 0, @holidaysCount);
UPDATE TX SET TX.'ETA' = @ETA_CFD, TX.'Start Date' = @CFD WHERE '#' = @CNT;
SET @days = @days + 1;
SET @daysNext = @days + 2 * ((@days / 5)::integer) +
(IF(@CFD::Date->getDay() + @days % 5 >= 6, 2,0));
SET @CFD = IF(@holidaysCount = 0, DATEADD(day, @daysNext, @CFD), @CFD);
SET @CNT = @CNT - IF(@holidaysCount = 0, 0, 1);
END;

SELECT T1.*,
COALESCE(TX.'Start Date', T1.'Start Date') AS 'Start Date',
FORMATDATE(TX.'ETA') AS 'ETA'
FROM T1
LEFT JOIN TX ON T1.'Milestone' = TX.'Milestone'

Hope it helps your case.

Best regards,

Alexey