Table Transformer - Working Days Except Public Holidays

[Craig Buchanan]

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:

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:

This is obviously incorrect 

[Katerina Kovriga _Stiltsoft_]

Hi @Craig Buchanan ,

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