Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi folks,
here I want to calculate working hours only for Saturday, I was tried too many thread but not found for Saturday working hours.
Suppose I have
Start_Date : 06/16/2018 10:40:30 AM, 06/23/2018 09:15:30 AM, 06/30/2018 07:30:35 AM
End_Date : 06/16/2018 12:00:05 PM, 06/23/2018 17:10:05 PM, 07/01/2018 00:00:35 AM
Working Hours time
Start_Time : 08 am
End_Time : 17 pm
Lunch_Start : 13 pm
Lunch_End : 13:30 pm
Thanks for advanced and immediate response
Regards
Rakesh
Now I using this script which is well worked for Monday to Friday,
but the requirement is for Saturday calculation between Start Hours 8:30 am to End 17:30 pm
LET vSatStartHour = 8;
LET vSatEndHour = 17;
LET vSatWorkingHourPerDay = ($(vSatEndHour) -$(vSatStartHour))-($(vLunchEnd)-$(vLunchStart));
LET vStartHour = 12;
LET vEndHour = 21;
LET vLunchStart =0;
LET vLunchEnd =0;
LET vWorkingHourPerDay = ($(vEndHour) -$(vStartHour))-($(vLunchEnd)-$(vLunchStart));
Holidays:
LOAD Concat(chr(39)&Holidays&chr(39),',') as Holidays Inline [
Holidays
08/04/2018
25/04/2018
];
LET vHolidays = Peek('Holidays',0,'Holidays');
SLA_Analysis:
LOAD *,
Interval(rangesum(round( rangesum(
NetWorkDays(START_TIME+1,END_TIME-1,$(vHolidays)) * MakeTime($(vWorkingHourPerDay)), // 12 hours per workday, for all day inbetween the period, excluding bounderies
if(NetWorkDays(END_TIME,END_TIME,$(vHolidays)) ,
rangesum(rangemin(frac(END_TIME),MakeTime($(vLunchStart)))- rangemin(frac(END_TIME),MakeTime($(vStartHour))) ,
rangemin(frac(END_TIME),MakeTime($(vEndHour))) - rangemin(frac(END_TIME),MakeTime($(vLunchEnd)))),0), // working hours last day
if(NetWorkDays(START_TIME,START_TIME,$(vHolidays)),
rangesum(MakeTime($(vLunchStart)) - rangemin(rangemax(frac(START_TIME), MakeTime($(vStartHour))),MakeTime($(vLunchStart))),
MakeTime($(vEndHour)) - rangemax(rangemin(frac(START_TIME), MakeTime($(vEndHour))),MakeTime($(vLunchEnd)))),0), // working first day
if(NetWorkDays(START_TIME,START_TIME,$(vHolidays)) and floor(START_TIME)=floor(END_TIME),-MakeTime($(vWorkingHourPerDay))) // If the same day then correct the hours
)*24,0.01))/24,'mm') AS Exclude_Saturday,
LOAD *,
timestamp(timestamp#(START_DATE,'MM/DD/YYYY hh:mm:ss TT')) as START_TIME,
timestamp(timestamp#(END_DATE,'MM/DD/YYYY hh:mm:ss TT')) as END_TIME;
Hope you can try like this.
Ex:-
Interval match(Actual Date) Load StartDate, EndDate
Resident AssemblyLine;
Now I using this script which is well worked for Monday to Friday,
but the requirement is for Saturday calculation between Start Hours 8:30 am to End 17:30 pm
LET vSatStartHour = 8;
LET vSatEndHour = 17;
LET vSatWorkingHourPerDay = ($(vSatEndHour) -$(vSatStartHour))-($(vLunchEnd)-$(vLunchStart));
LET vStartHour = 12;
LET vEndHour = 21;
LET vLunchStart =0;
LET vLunchEnd =0;
LET vWorkingHourPerDay = ($(vEndHour) -$(vStartHour))-($(vLunchEnd)-$(vLunchStart));
Holidays:
LOAD Concat(chr(39)&Holidays&chr(39),',') as Holidays Inline [
Holidays
08/04/2018
25/04/2018
];
LET vHolidays = Peek('Holidays',0,'Holidays');
SLA_Analysis:
LOAD *,
Interval(rangesum(round( rangesum(
NetWorkDays(START_TIME+1,END_TIME-1,$(vHolidays)) * MakeTime($(vWorkingHourPerDay)), // 12 hours per workday, for all day inbetween the period, excluding bounderies
if(NetWorkDays(END_TIME,END_TIME,$(vHolidays)) ,
rangesum(rangemin(frac(END_TIME),MakeTime($(vLunchStart)))- rangemin(frac(END_TIME),MakeTime($(vStartHour))) ,
rangemin(frac(END_TIME),MakeTime($(vEndHour))) - rangemin(frac(END_TIME),MakeTime($(vLunchEnd)))),0), // working hours last day
if(NetWorkDays(START_TIME,START_TIME,$(vHolidays)),
rangesum(MakeTime($(vLunchStart)) - rangemin(rangemax(frac(START_TIME), MakeTime($(vStartHour))),MakeTime($(vLunchStart))),
MakeTime($(vEndHour)) - rangemax(rangemin(frac(START_TIME), MakeTime($(vEndHour))),MakeTime($(vLunchEnd)))),0), // working first day
if(NetWorkDays(START_TIME,START_TIME,$(vHolidays)) and floor(START_TIME)=floor(END_TIME),-MakeTime($(vWorkingHourPerDay))) // If the same day then correct the hours
)*24,0.01))/24,'mm') AS Exclude_Saturday,
LOAD *,
timestamp(timestamp#(START_DATE,'MM/DD/YYYY hh:mm:ss TT')) as START_TIME,
timestamp(timestamp#(END_DATE,'MM/DD/YYYY hh:mm:ss TT')) as END_TIME;