Personal Insights

# Calculate the total number of Business days present in a Month as per Organization/Company calendar

Introduction

Hello Everyone,

The purpose of creating this table function is to calculate the  key figures which are very important for Target Vs Sales Analytical reporting. This Key figures can be referenced in Predictive Sales reporting.

The functionality of the following  four key figures :-

1. Total No of Business/Working days (“Total_WDS”) in a January 2020  is “22”. Please refer the below calendar snapshot. Count the yellow highlighted box which is an Working day as per calendar. And red border highlighted box i.e 23rd Jan,2020 is an Holiday. Output : 2.  Is “Today Working ??(TODAY_WD”)-  The Input Date is a business day or an Holiday. This measure output is boolean (“Y/N”). For e.g input date 23rd Jan, 2020  will return “N” as  this  is an public Holiday.

output: 3. “Current working days”(“CRNT_WD”) – Returns the Current no of working days. For e.g Input date  20th Jan, 2020, no of working day is “14” . Count the yellow color highlighted box. Output: 4. “Working Day in %(Percentage)” (WDS_PRCNT)-   The formula is

(“CRNT_WD”/”TOT_WDS”)*100 Use Case of the following key Figures:

I have addressed the requirement of Sales business reporting. This above calculated key figures can be referenced in a “Predictive KPI” calculation. For e.g if a  Sales Person sold an x no. of material  till 7th Business day of the month then how many more no of material can he/she sell in the remaining no of working days.

This requirement was very common in “Target Vs Actual Sales” reporting requirement. So I Shared this code with you all.

This table function can be consumed directly in the SQL code, Stored Procedure or any Calculation Views. In my case i consumed in a calculation view.

Input Parameter :  One Input Parameter of Varchar data type of Size 11 (Date format  ‘2020-01-20’)

HANA Table Function Code:

FUNCTION  TF_XXXX_XXXX_XXXXX (IN “IP_YYYYMMDD” VARCHAR(11))

RETURNS TABLE

(“TOT_WDS” TINYINT,
“CRNT_WD” TINYINT,
“TODAY_WD” VARCHAR(10),
WDS_PRCNT DECIMAL(5,2))

LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER AS

/*****************************
*****************************/

T_YEAR VARCHAR(4);
T_MTH VARCHAR(2);
T_DAYS TINYINT;
I SMALLINT;
I_CRNT_DAY NVARCHAR(2);
C_YR VARCHAR(4);
C_MTH VARCHAR (2);
SQL_COL NVARCHAR(256);
T_MNTH_RSLT NVARCHAR(31);
T_WDS TINYINT;
C_WD TINYINT;
TODAY_WD NVARCHAR (1);
TOT_WDS TINYINT;
CRNT_WD TINYINT;

CURSOR CS2
FOR SELECT “MON01”, “MON02”, “MON03”, “MON04”, “MON05”, “MON06”, “MON07”, “MON08”, “MON09”, “MON10”, “MON11”, “MON12”
FROM “XXXXX”.”XXXXX”  —  You have to use you own Company Leave calendar table
WHERE IDENT = ‘xxxx’ Your Country code Filter
AND JAHR = T_YEAR;  — Year Filter

BEGIN

I_CRNT_DAY := RIGHT(“IP_YYYYMMDD”,2);
C_YR := LEFT(“IP_YYYYMMDD”,4);
C_MTH := SUBSTRING(“IP_YYYYMMDD”,6,2);

T_YEAR := LEFT(“IP_YYYYMMDD”,4);
T_MTH := SUBSTRING(“IP_YYYYMMDD”,6,2);
TODAY_WD := ‘ ‘;
If ((T_MTH = ’01’) OR (T_MTH = ’03’) OR (T_MTH = ’05’) OR (T_MTH = ’07’) OR (T_MTH = ’08’) OR (T_MTH = ’10’) OR (T_MTH = ’12’)) then
T_DAYS := 31;
ELSEIF ((T_MTH = ’02’) OR (T_MTH = ’04’) OR (T_MTH = ’06’) OR (T_MTH = ’09’) OR (T_MTH = ’11’)) then
T_DAYS := 30;
ELSE
TOT_WDS := 0;
CRNT_WD := 0;
TODAY_WD := ‘X’;
END IF;

If “TODAY_WD” = ‘ ‘ then
If (T_MTH = ’02’) then
T_DAYS := 28;
I = MOD(T_YEAR, 4);
if I = 0 then
T_DAYS := 29;
END IF;
END IF;

For R2 AS CS2 DO
If T_MTH = ’01’ Then T_MNTH_RSLT := R2.MON01;
elseif T_MTH = ’01’ Then T_MNTH_RSLT := R2.MON01;
elseif T_MTH = ’02’ Then T_MNTH_RSLT := R2.MON02;
elseif T_MTH = ’03’ Then T_MNTH_RSLT := R2.MON03;
elseif T_MTH = ’04’ Then T_MNTH_RSLT := R2.MON04;
elseif T_MTH = ’05’ Then T_MNTH_RSLT := R2.MON05;
elseif T_MTH = ’06’ Then T_MNTH_RSLT := R2.MON06;
elseif T_MTH = ’07’ Then T_MNTH_RSLT := R2.MON07;
elseif T_MTH = ’08’ Then T_MNTH_RSLT := R2.MON08;
elseif T_MTH = ’09’ Then T_MNTH_RSLT := R2.MON09;
elseif T_MTH = ’10’ Then T_MNTH_RSLT := R2.MON10;
elseif T_MTH = ’11’ Then T_MNTH_RSLT := R2.MON11;
else T_MNTH_RSLT := R2.MON12;
end if;
end for;

T_WDS := 0;
C_WD := 0;
TODAY_WD := ‘N’;

FOR i in 1..T_DAYS do

If SUBSTRING(T_MNTH_RSLT,i,1) = 1 THEN
T_WDS := T_WDS + 1;
if i <= I_CRNT_DAY THEN
C_WD := C_WD + 1;
end if;
if i = I_CRNT_DAY THEN
TODAY_WD := ‘Y’;
end if;
end if;

END FOR;

If T_YEAR < C_YR then
C_WD := T_WDS;
elseIf T_YEAR > C_YR then
C_WD := 0;
elseIF T_MTH < C_MTH then
C_WD := T_WDS;
elseIf T_MTH > C_MTH then
C_WD := 0;
end if;
end if;

“TOT_WDS” := T_WDS;
“CRNT_WD” := C_WD;
“TODAY_WD” := TODAY_WD;

Return
select sum(“TOT_WDS”)as “TOT_WDS”,sum(“CRNT_WD”)as “CRNT_WD” , max(“TODAY_WD”)as “TODAY_WD”,(Sum(“CRNT_WD”)/sum(“TOT_WDS”)*100) as WDS_PRCNT
from(
select “TOT_WDS” as “TOT_WDS” ,0 as “CRNT_WD”, ” “TODAY_WD” from Dummy
union
select 0,”CRNT_WD”,” from Dummy
union
select 0,0,”TODAY_WD” from Dummy);

END;

Screenshot of the Function Output:

• Snapshot of Input Parameter Screen • Snapshot of Calculation View Output. Conclusion –

This Table Function will return the no of working days and the Current Working day as per employee Calendar in a specific month. This calculated key figures can be references  can be consumed for Predictive calculation and any Sales fact table.

Thanks I hope you will like this blog post. and will be helpful.

/        1 Comment
You must be Logged on to comment or reply to a post.
• Very helpful and very well explained with detailed screenshots and code. Thanks.