How to check the number of public holidays days found between 2 dates

[Anonymous]

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

[tresesco]

Have you tried NetWorkDays()?

[#3]

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

[Anonymous]

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

[Anonymous]

Networkdays is not taking into consideration holidays

[#6]

You need to add the optional parameter for holidays like I said

[tresesco]

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.

[#8]

I should add, you'll want to put your holiday list into a variable and use that as the holiday optional parameter

[Anonymous]

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

[#10]

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.