Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in
Celebration

Earn badges and make progress

You're on your way to the next level! Join the Kudos program to earn points and save your progress.

Deleted user Avatar
Deleted user

Level 1: Seed

25 / 150 points

Next: Root

Avatar

1 badge earned

Collect

Participate in fun challenges

Challenges come and go, but your rewards stay with you. Do more to earn more!

Challenges
Coins

Gift kudos to your peers

What goes around comes around! Share the love by gifting kudos to your peers.

Recognition
Ribbon

Rise up in the ranks

Keep earning points to reach the top of the leaderboard. It resets every quarter so you always have a chance!

Leaderboard

Come for the products,
stay for the community

The Atlassian Community can help you and your team get more value out of Atlassian products and practices.

Atlassian Community about banner
4,463,507
Community Members
 
Community Events
176
Community Groups

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

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. 

image.png

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

image.png 

Thanks in advance for your help.

1 answer

Hi @velmurugan

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

Mon 1-1.png

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'

Mon 1-2.png

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

Hi @velmurugan,

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

Fri 4-1.png

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'

Fri 4-2.png

Hope it helps your case.

Best regards,

Alexey

Like Stiltsoft support likes this

Suggest an answer

Log in or Sign up to answer
TAGS

Atlassian Community Events