Network Days in reference to a Calendar table with a workday indicator

[Gary1]

I am trying to find Network days in hours for different customers operating from different locations in reference to  a given transaction and Calendar Table with same customers operating from different locations and a workday indicator of 0 for weekends and holidays and 1 for working days.The SLA is 24  hrs . We need to consider only the working days with indicator 1. 

Please see below a sample of each table below and note the holiday schedule is different for different locations.

Cutomer	Location	Receiving Timestamp	End Time Stamp	Total Tme Taken in Hrs
A	X	01/05/2022 12:16:07	01/08/2022 12:33:07	Network days in reference to the Calendar Table and the Calendar ID
A	Y
B	X
C	X
D	Z

Customer	Location	Calendar ID	Calendar Day	Workday Indicator	Day
A	X	PQ	1/1/2022	0	Weekend
A	Y	RS	1/5/2022	1	Weekday
D	Z	TU	2/19/2022	0	Holiday

[vinieme12 · Accepted solution]

As below;

tempHoliday:
Load * inline [
Customer,Location,CalendarID,CalendarDay,Workday Indicator,Day
A,X,PQ,1/1/2022,0,Weekend
A,Y,RS,1/5/2022,1,Weekday
D,Z,TU,2/19/2022,0,Holiday
];

holidaylist:
load Concat(chr(39) & date#(CalendarDay,'M/D/YYYY') & chr(39),',') as list
Resident tempHoliday;
drop table tempHoliday;

let vHolidayList = peek('list',0,'holidaylist');
drop table holidaylist;

temp:
Load
*
,interval(((networkDays*24*3600) - AdjStart-AdjEnd)/86400,'hh:mm:ss') as NetworkHours_formatted
,((networkDays*24) - AdjStart/3600 -AdjEnd/3600) as NetworkHours_decimal
;
Load *
,NetWorkDays(date(ReceivingTimestamp),date(EndTimeStamp),$(vHolidayList)) as networkDays
,if(WildMatch(WeekDay(date(ReceivingTimestamp)),'Mon','Tue','Wed','Thu','Fri'),frac(ReceivingTimestamp)*(24*3600),0) as AdjStart
,if(WildMatch(WeekDay(date(EndTimeStamp)),'Mon','Tue','Wed','Thu','Fri'),86400-frac(EndTimeStamp)*(24*3600),0) as AdjEnd
;
// Just comverting text to timestamp in load statement below
Load
Cutomer,Location
,Timestamp#(ReceivingTimestamp,'mm/dd/yyyy hh:mm:ss') as ReceivingTimestamp
,Timestamp#(EndTimeStamp,'mm/dd/yyyy hh:mm:ss') as EndTimeStamp
inline [
Cutomer,Location,ReceivingTimestamp,EndTimeStamp
A,X,01/05/2022 12:16:07,01/08/2022 12:33:07
B,X,01/05/2022 12:16:07,01/07/2022 12:33:07
C,X,02/05/2022 13:18:09,02/21/2022 12:33:07
];
exit Script;

Output