Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello All,
I am trying to find out the working days in a month for current year, for that i am wring the below script.
i am getting two records for each month one is total no.of working days (Excluding Weekends) and second is after holidays calculation.
but i need only one record that holidays include.
Script:
temp_network_days:
Load distinct [Call Date],
MonthStart([Call Date]) as monthstart1,
MonthEnd([Call Date]) as monthend1
Resident MasterCalendar
where [Call Date]>= Date(YearStart(Today()), 'DD/MM/YYYY'); // need to automate
left join(temp_network_days)
Load date(date#(Date1,'DD/MM/YYYY'),'DD/MM/YYYY') as Date1,
date(date#(Date1,'DD/MM/YYYY'),'DD/MM/YYYY') as [Call Date];
Load * inline [
Date1,Day,Holiday
1/1/2022,Sat,New Year's Day
2/1/2022,Sun,Berchtold's Day
6/1/2022,Thu,Epiphany
1/3/2022,Tue,Republic Day
19/3/2022,Sat,St Joseph's Day
7/4/2022,Thu,Näfelser Fahrt
15/4/2022,Fri,Good Friday
18/4/2022,Mon,Easter Monday
1/5/2022,Sun,Labour Day
26/5/2022,Thu,Ascension Day
6/6/2022,Mon,Whit Monday
16/6/2022,Thu,Corpus Christi
23/6/2022,Thu,Independence of Jura
29/6/2022,Wed,St Peter and St Paul
1/8/2022,Mon,National Day
15/8/2022,Mon,Assumption Day
8/9/2022,Thu,Jeûne genevois
19/9/2022,Mon,Day after the Federal Fast
22/9/2022,Thu,St Mauritius
25/9/2022,Sun,Brother Klaus Festival
1/11/2022,Tue,All Saints' Day
8/12/2022,Thu,Immaculate Conception
24/12/2022,Sat,Christmas Eve
25/12/2022,Sun,Christmas Day
26/12/2022,Mon,St Stephen's Day
26/12/2022,Mon,Christmas Holiday
31/12/2022,Sat,New Year's Eve
];
network_days:
Load distinct [Call Date] ,
Date1,
monthstart1,
monthend1,
NetWorkDays(monthstart1,monthend1,[Call Date]) as No_of_days
Resident temp_network_days;
drop table temp_network_days;
current Output:
expected output is single record for month, which are highlighted in yellow for eg.
Thanks in Advance.