Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
HI All,
How to calculate Holidays and Sunday in Between 2 days (In_Date and Out_Date)
calculate Holidays count...........
Table1:
LOAD * INLINE [
Token_Num, In_Date, Out_Date
100, 1/1/2014, 1/5/2014
101, 1/9/2014, 1/15/2014
102, 1/12/2014, 1/23/2014
103, 1/22/2014, 1/31/2014
104, 1/25/2014, 2/5/2014
105, 2/6/2014, 2/10/2014
106, 2/12/2014, 2/14/2014
107, 2/15/2014, 2/22/2014
108, 2/23/2014, 2/27/2014
109, 3/1/2014, 3/5/2014
110, 3/10/2014, 3/15/2014
];
Holiday_List:
Thanks in Advance................
Try this :
SET ThousandSep='.';
SET DecimalSep=',';
SET MoneyThousandSep='.';
SET MoneyDecimalSep=',';
SET MoneyFormat='€ #.##0,00;€ -#.##0,00';
SET TimeFormat='hh:mm:ss';
SET DateFormat='M/D/YYYY';
SET TimestampFormat='D/MM/YYYY hh:mm:ss[.fff]';
SET MonthNames='janv.;févr.;mars;avr.;mai;juin;juil.;août;sept.;oct.;nov.;déc.';
SET DayNames='lun.;mar.;mer.;jeu.;ven.;sam.;dim.';
LOAD Date#(Date, 'D/M/YYYY') AS Date_Off, *; // Sync Dates format
Load * Inline [
Date, Holiday
7/1/2014, Guru Govind Singh Jayanti
14/1/2014, Pongal
26/1/2014, Republic Day
31/1/2014, Chinese New Year
4/2/2014, Vasant Panchami
9/2/2014, Guru Ravidas Jayanti
14/2/2014, Valentine's Day
Left join (Table1)
IntervalMatch (Date_Off) LOAD In_Date, Out_Date Resident Table1;
Table1_Final:
LOAD Token_Num, In_Date, Out_Date,
count(Token_Num) AS NumberOfDaysOff
Resident Table1
Group by Token_Num, In_Date, Out_Date;
Drop Table Table1;
Try the function NetWorkDays()
NetWorkDays (start:date, end_date {, holiday})
Returns the number of working days (Monday-Friday) between and including start_date and end_date taking into account any optionally listed holidays. All parameters should be valid dates or timestamps.
Examples:
NetWorkDays('2007-02-19', '2007-03-01') returns 9
NetWorkDays('2006-12-18', '2006-12-31', '2006-12-25', '2006-12-26') returns 8
Best Regards.
Tonial.
HI Michael,
i want calculate no.of holidays Count
Suppose Example
ID Date1 Date2 Count_of_Holidays
101, 1/9/2014, 1/15/2014 4 (Sat and Sun=2 and 7, 14 holidays)
102, 1/13/2014, 1/23/2014 2 (Sat and Sun=2)
Hope you understand............
You try this:
(Date2-Date1)-NetWorkDays(Date1,Date2,'7/1/2014','14/1/2014','26/1/2014','31/1/2014','4/2/2014','9/2/2014','14/2/2014')
