Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I have 2 dates, date_In and date_Out. I also have a list of bank holidays appended in a list ('holiday1,holiday2,holiday3')
I would like to know if there is a way to calculate in the script how many public holidays there are between date_in and date_out.
Thanks
Hasnaa
Have you tried NetWorkDays()?
Hi Hasnaa,
If you are looking to do this in the script have a look at the networkdays function. If you have a list of holidays, you can plug this in to the function.
So something like:
(Networkdays([date_in],[date_out])
-
Networkdays([date_in],[date_out], [your list of holidays here]) ) As Holiday_counter
In the example, the first function gives you all working days between the range, then used again you can exclude your defined holiday list, the net of the two is the number of holidays that fall between the dates
hope that helps
Joe
Networkdays will actually calculate the difference between the two days. I have tried and it is not even excluding the holodays. What I really need is to get only the number of holidays that falls between the two dates.
Thanks
Hasnaa
Networkdays is not taking into consideration holidays
You need to add the optional parameter for holidays like I said
One way could be like, create multiple dates using SubField() like:
HDays:
Load
Date#(SubField(PurgeChar(HolidayDates, chr(39)), ','), 'MM/DD/YYYY') as Holidays
From <>;
Then use cross join this table with rest of the table. Then you would get all the holidays against each of the date combination(in and out). Then you just have to compare them with simple IF like:
Load
Count(If(InDate<=Holidays and outDate>=Holidays,1)) as Count
or may be you can try with intervalmatch() as well.
I should add, you'll want to put your holiday list into a variable and use that as the holiday optional parameter
Hi
Thats what I did. However, the network days function is not taking into consideration the holidays. I used the following in the script:
SET TimeFormat='hh:mm:ss';
SET DateFormat='MM/DD/YYYY';
SET TimestampFormat='MM/DD/YYYY hh:mm:ss[.fff]';
Returns_Date:
LOAD _Id_Operator AS OPERATOR_CODE,
Return_ID,
Time_In,
Time_Out
FROM
(biff, embedded labels, table is [Sheet1$]);
tmpHoliday :
LOAD
OPERATOR_CODE,
if([WORKINGDAY]='N',Date([CALDATE])) as Date
FROM
$(v_QVD)$(v_qvd_calendar)(qvd);
tmpConcat :
LOAD
OPERATOR_CODE,
concat(chr(39) & Date & chr(39),',') as HolidayDates
RESIDENT
tmpHoliday
GROUP BY OPERATOR_CODE;
Join(tmpConcat)
LOAD
OPERATOR_CODE,
Return_ID,
Time_In,
Time_Out
Resident Returns_Date;
TEST:
LOAD *,
NetWorkDays(Time_In, Time_Out) as `TAT NOholiday`,
NetWorkDays(Time_In, Time_Out, HolidayDates) as `TAT holiday`
RESIDENT tmpConcat;
The Time_In and Time_Out are in the format 'MM/DD/YYYY hh:mm:ss'. The holidays are in the format 'MM/DD/YYYY'. Could this be the issue?
Kindly advise
Thanks
Hasnaa
Yes I would also date your time fields in the network days and see how that goes
Also is there a need to join in like that? Do the holiday dates differ per operator_code? If not I would load that list as a variable from the temp table and use that as the parameter, to save the need of the join.