Holiday and week end - leave calculation

[Anonymous]

Hi All,

Please help me in finding No of Working days for a dealer, we have a holiday list and sunday should be consider as holiday.

Example :

  Dealer  - A     Holiday -  01 - Jan -15

  Dealer - B      Holiday  - 3 - APR- 15

We already have master calendar, from that calendar the above holiday days and sunday should be considered as holiday.

Thks in advance

[jpenuliar]

You should have a Holidays table (including sundays) to link or Flag  to your Master Calendar

[Anonymous]

you already defined holiday list in you calendar or you need to define ??

[Anonymous]

Load a table with holiday dates then use below code in your master calendar :

//******* Holiday********

tblHoliday:

LOAD Date(Holiday) as Holiday

from HolidayList;

/************** varHoliday **************/

tmpTbl:

NoConcatenate

LOAD Concat(Holiday,',') as fieldHoliday

Resident tblHoliday;

LET varHoliday = Peek('fieldHoliday',0,'tmpTbl');

DROP Table tmpTbl;

//Script One

Let varMinDate = num(MakeDate(2000,1,1));

Let varMaxDate = num(MakeDate(2020,12,31));

//*************** Temporary Calendar ***************

TempCalendar:

LOAD

  $(varMinDate) + rowno() - 1 AS Num,

  date($(varMinDate) + rowno() - 1) AS TempDate

AUTOGENERATE

  $(varMaxDate) - $(varMinDate) + 1;

//*************** Master Calendar ***************

MasterCalendar:

LOAD TempDate AS CalDate,

  Date(TempDate) AS CalanderDate,

  //If(weekday(TempDate)='Sat' or weekday(TempDate)='Sun',0,1) as PlannedBD2,

  If(weekday(TempDate)='Sat' or weekday(TempDate)='Sun' or If(substringcount('$(varHoliday)',Date(TempDate))>0,1,0),0,1) as PlannedBD2,

  1 As PlannedBD,