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

It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

Table Transformer - Working Days Except Public Holidays

Hi,

I am trying to get an example working from the Stiltsoft website working and can't get it to work to save myself. The SQL is to calculate the number of working days between two dates less public holidays.

The location of the exact use case on the website can be found: https://docs.stiltsoft.com/display/public/TFAC/Custom+Transformation+-+use+cases+with+advanced+SQL+queries

However when I try and recreate this below is the result I get:

Capture.PNG

SQL Query:

SELECT *,
'Days' - 2 * (('Days' / 7)::integer) -
CASE
WHEN 'Days' % 7 = 0 THEN 0
WHEN 'Start date'::Date->getDay() = 0 THEN 1
WHEN 'Start date'::Date->getDay() + 'Days' % 7 = 6 THEN 1
WHEN 'Start date'::Date->getDay() + 'Days' % 7 > 6 THEN 2
ELSE 0
END
-
(SELECT COUNT(*) FROM
(SELECT *,'Holiday'::Date->getDay() AS 'Day' FROM T2)
WHERE 'Holiday' >= TT.'Start date' AND 'Holiday' <= TT.'End date'
AND 'Day' > 0 AND 'Day' < 6)
AS 'Work days'
FROM
(SELECT *, ('End date' - 'Start date') / "1d" + 1 AS 'Days' FROM T1) AS TT

Result:

Capture.PNG

This is obviously incorrect 

 

1 answer

Hi @Craig Buchanan ,

You need to set your worklog settings as following to make the query work properly:

Tue 2-1.png

Tue 2-2.png

Tue2-3.png

Suggest an answer

Log in or Sign up to answer
TAGS
Community showcase
Published in Confluence Cloud

🎨 Add some visual life to your templates

Hi Atlassian Community, My name is Avni Barman, and I am a Product Manager on the Confluence Cloud team. Based on feedback from you, we are giving admins more power to create templates that a...

259 views 5 9
Read article

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you