cancel
Showing results for
Did you mean:
See all
See all
##### Top groups
Explore all groups

## 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

Level 1: Seed

25 / 150 points

Next: Root

## Participate in fun challenges

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

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

## 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!

## 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.

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 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

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

Hi @velmurugan,

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'