If you only have dates for start and end date, you will first need to somehow create a date record for each day in your period. If you could calculate the networkdays in the script, you can do it like this:
Set TimestampFormat = 'M/D/YY hh:mm TT';
Set vHol = '41160,41162';
LOAD *, recno() as ID INLINE [
9/1/12 11:08 AM,9/8/12 2:57 PM
8/18/12 09:20 AM,8/20/12 01:13 PM
8/17/12 09:20 AM,8/20/12 01:13 PM
8/17/12 09:20 AM,8/19/12 01:13 PM
6/27/12 12:41 PM, 7/6/12 4:38 PM
6/29/12 4:45 PM, 7/6/12 4:19 PM
// Create a record per day in your periods
Date(daystart(DT1)+iterno()-1) as Date
while daystart(DT2) >= daystart(DT1)+iterno()-1;
// calculate the sum of days in your periods, that are not a Thu or Fri, or part of your Holidays (set variable vHol)
left join (INPUT) LOAD
count(Date) as Networkdays
Resident TMP where (WeekDay(Date)<3 or WeekDay(Date)>4) and not match(Date,$(vHol)) group by ID;
drop table TMP;
The Weekday() function returns 3 for Thursday and 4 for a Friday.
Hope this helps,
as i see it it works but it creates raws for each day in between the dates in each row of the source table.
if my source table is big and in avg i have 5-10 days diff in each row it means ill need to create a table 5-10 times larger.
this might be a problem
any other ways?
i need networkdays function to give sunday-thursday as working days (excluding friday and saterday)
what will the master-calendar help here?
i have a fact with 2 date in each row and i need to calculate the working days between these dates.
i see no way to use the master-calendar in this scenario
normally id use the function NETWORKDAYS() but this functions assumes week where Monday is first day of the week and something that can work with weeks where Sunday is first day and Friday and Saturday are non working days.
solution was the best i have found so far
(except the issue with large fact table about which i asked)
I don't know what do you want to do with these networkdays-values. If you don't calculate these values further within the script it could be better to perform such calculation on a small amount of data (restricted by selections) within the gui.
Nevertheless you could create an expression with a main-logic like this:
(enddate - startdate) - floor((enddate - startdate) / 7)
with an additional considering from the weekday from the startdate and enddate and a little bit fine-tuning.
I have built such expression some time ago but actually couldn't find it. Good luck.