I'm using a table transformer to just calculate week days only. I had some very complicated SQL and this wasn't working reliably (with some weird rounding going on), and then I realised DATEDIFF offered a weekday option - but it doesn't seem to be discounting the weekends.
For example, I was using this:
SELECT DATEDIFF(day, "01/01/2024", NOW())
- 2 * (DATEDIFF(day, "01/01/2024", NOW()) / 7)
- CASE WHEN DAYOFWEEK("01/01/2024") IN (0, 6) THEN 1 ELSE 0 END
- CASE WHEN DAYOFWEEK(NOW()) IN (0, 6) THEN 1 ELSE 0 END
AS 'Days since start of year, excluding weekends'
But as I tested some future dates by swapping NOW() with literal dates, such as "03/02/2024", the calculation was not reliable. The date format I am using in the table transformer is dd/mm/yyyy
If I try and use this, then I don't get back the number of weekdays, only the full number of days. For example, today 29/01/2024 this is returning 28.
SELECT DATEDIFF(weekday, "01/01/2024", NOW())
AS 'Days since start of year, excluding weekends'
How do I just get the number of weekdays, Monday to Friday?
Ok, this seems to work. I've used some variables and hard-coded dates to try this out.
DECLARE @StartDate DATETIME;
DECLARE @EndDate DATETIME;
SET @StartDate = "01/01/2024";
SET @EndDate ="05/02/2024";
SELECT
(DATEDIFF(day, @StartDate, @EndDate) + 1)
-(DATEDIFF(week, @StartDate, @EndDate) * 2)
-(CASE WHEN DAYOFWEEK(@StartDate) = 6 THEN 2 ELSE 0 END)
-(CASE WHEN DAYOFWEEK(@StartDate) = 0 THEN 1 ELSE 0 END)
-(CASE WHEN DAYOFWEEK(@EndDate) = 6 THEN 1 ELSE 0 END)
-(CASE WHEN DAYOFWEEK(@EndDate) = 0 THEN 2 ELSE 0 END) as 'Counting week days only'
This solution doesn't work. I am struggling with the same problem. It seems every solution I find works for most but not all scenarios. Try 01/02/2025 and 01/15/2025 as your dates. Result will be 12 days, when it should be 10.
Remember to check the workday settings in Table Transformer. It defaults to a 5 day workweek, but any of these solutions won't work unless you change the settings to indicate a "week" is 7 days, 24 hours.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi @Scott Gillespie ,
You may an example from our documentation regarding the topic.
If it works not as expected for you or if your case is a more complicated one, please refer to our support portal.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
I can see that the approach in the Confluence documentation mentioned does give the result of 10, when trying this out with start date 01/02/2025 and end date 01/15/2025.
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)
In my use case, I just wanted to adapt this to calculate the start and end date dynamically, and not from data in the table. The end date is always today, and the start date is either 30 days previously or the first day of the year. This is to give 30 day rolling periods, or otherwise as many days have so far occurred, in the calendar year. I use these sort of calculations in metrics for the calendar year.
So, I tried to use variables to substitute the 'Start date' and 'End date' columns, as per the below. Unfortunately, this returns no rows. Maybe it is something reasonably straightforward syntactically or logically? Anyone, any ideas?
DECLARE @StartDate DATETIME = GREATEST(DATEADD(day,-30, GETDATE()),"01/01/2025");
DECLARE @EndDate DATETIME = GETDATE();
SELECT 'Days' -
2 * (('Days' / 7)::integer) -
CASE
WHEN 'Days' % 7 = 0 THEN 0
WHEN @StartDate::Date->getDay() = 0 THEN 1
WHEN @StartDate::Date->getDay() + 'Days' % 7 = 7 THEN 1
WHEN @StartDate::Date->getDay() + 'Days' % 7 > 7 THEN 2
ELSE 0
END AS 'Work days'
FROM
(SELECT *,
ROUND((@EndDate - @StartDate) / "1d") + 1 AS 'Days'
FROM T1)
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Please create a support request - we'll try to look into the case more closely.
Meanwhile, not all JS methods are supported by the Table Transformer macro, it's based on the AlaSQL library and supports its syntax.
So you may try to simplify your case:
Not sure what you mean by "not from data in the table" but the macro works with tables. So, you may create a blank one row table inside the macro body and populate it with the today date and calculated start date using the SQL query.
Then in a separate Table Transformer on top you'll be able to use the query from the documentation.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Ok, thanks. I use the Table Transformer, as it gives me the ability to access and leverage SQL type language in Confluence and completely dynamically calculate values. I can also use this in combination with actual table data, in different use cases.
For example, if my Table Transformer has a completely empty table, I can still do this.
I also think this logic is now working, having tried a few more things.
The working days (Monday to Friday) calculated between 1 January 2025 and 15 January 2025 is being returned as 11. @Scott Gillespie Maybe check this out, I also hard coded 2 January as start date and 15 January as end date and this returns 10, inclusive of the start and end dates. I haven't tested anything other than these two scenarios!
DECLARE @StartDate DATETIME = FORMATDATE(GREATEST(DATEADD(day,-30, GETDATE()),"01/01/2025"), "yy-mm-dd");
DECLARE @EndDate DATETIME = FORMATDATE(GETDATE() ,"yy-mm-dd");
DECLARE @Days INT = DATEDIFF(day, @StartDate, @EndDate);
DECLARE @WeekDays INT;
SET @WeekDays = (SELECT @Days -
2 * ((@Days / 7)::integer) -
CASE
WHEN @Days % 7 = 0 THEN 0
WHEN @StartDate::Date->getDay() = 0 THEN 1
WHEN @StartDate::Date->getDay() + @Days % 7 = 7 THEN 1
WHEN @StartDate::Date->getDay() + @Days % 7 > 7 THEN 2
ELSE 0
END) + 1;
SELECT @WeekDays AS 'Counted Value'
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.