Use case

You have a table containing a period of time (start date and end date). You need to count the number of workdays during this time period.

More Advanced option:

You have an additional table with national holidays which you also shouldn't consider as workdays.

Solution

  1. Switch the page to the edit mode.
  2. Insert the Table Transformer macro and paste the table within the macro body.
  3. Select the macro and click Edit.
  4. Switch to the SQL query tab.

  5. Enter the following 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 = 7 THEN 1
	WHEN 'Start date'::Date->getDay() + 'Days' % 7 > 7 THEN 2
	ELSE 0
END AS 'Work days'
FROM
(SELECT *,
ROUND((T1.'End date' - T1.'Start date') / "1d") + 1 AS 'Days'
FROM T1)
  6. Click Next.
  7. Set the worklog settings.
  8. Save the macro and the page.

You can use FORMATWIKI function for the purposes of cell formatting.

For the advanced use case option

Enter the following 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 = 7 THEN 1
	WHEN 'Start date'::Date->getDay() + 'Days' % 7 > 7 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 *, ROUND(('End date' - 'Start date') / "1d") + 1 AS 'Days' FROM T1) AS TT


  • No labels