We have “WORKDAYS_BETWEEN” In-built Function in HANA to calculate Business Days Between based on Organization Customization.
Prerequisite To use the Function is,
TFACS must be available in the SAP HANA database. (TFACS Table will be available in SAP ECC (on HANA) and BW (on HANA) Systems in SAP<SID> Schema. Else TFACS table would be replicated from a SAP ECC system)
For Example like this scenarios,
1. In Native HANA System, (TFACS table won’t be there) How can I Calculate Customized WORKDAYS_BETWEEN?
2. I have Table for Holiday List for My Organization. Using this Table only I have to Calculate WORKDAYS_BETWEEN. How can I?
This blog about this.
I am going to explain My experience on How I calculated WORKDAYS_BETWEEN.
My Input Data is,
I have to Calculate No.of Workdays From Jan 1 to Jan 31 based on Company leave Calendar.
Company Leave Calendar for Jan 2016:
Based on this Leave Calendar, No.of Work_Days Between Jan 1 to Jan 31 is 20.
To calculate WORKDAYS, based on this calendar, I have written the Procedure. I have given the procedure snippets below.
It give me the required output 🙂 .
[I have written the Procedure using CURSOR]
DECLARE CURSOR CURSOR_1 FOR
SELECT START_DATE, END_DATE FROM “SCHEMA1”.“DAYS_BETWEEN”;
LOOP_NUM := 1;
FOR CURRENT_ROW as CURSOR_1 DO
V_TOTAL_DAYS_COUNT := DAYS_BETWEEN (CURRENT_ROW.START_DATE, CURRENT_ROW.END_DATE);
SELECT COUNT(*) INTO V_HOLIDAYS_COUNT FROM “SCHEMA1”.“T004_HOLIDAY_CALENDAR” WHERE HOLIDAY_DATE BETWEEN CURRENT_ROW.START_DATE AND CURRENT_ROW.END_DATE;
SELECT COUNT(*) INTO V_ENDDATE_AS_HOLIDAY FROM “SCHEMA1”.“T004_HOLIDAY_CALENDAR” WHERE HOLIDAY_DATE = CURRENT_ROW.END_DATE;
IF V_ENDDATE_AS_HOLIDAY = 1 THEN
V_BUSINESS_DAYS_COUNT := (V_TOTAL_DAYS_COUNT – V_HOLIDAYS_COUNT) + 1;
V_BUSINESS_DAYS_COUNT := V_TOTAL_DAYS_COUNT – V_HOLIDAYS_COUNT;
ARR_START_DATE[LOOP_NUM] := CURRENT_ROW.START_DATE;
ARR_END_DATE[LOOP_NUM] := CURRENT_ROW.END_DATE;
ARR_BUSINESS_DAYS_COUNT[LOOP_NUM] := V_BUSINESS_DAYS_COUNT;
LOOP_NUM := LOOP_NUM + 1;
OUTPUT_TABLE = UNNEST (:ARR_START_DATE, :ARR_END_DATE, :ARR_BUSINESS_DAYS_COUNT)
AS (“START_DATE”, “END_DATE”, “WORKDAYS_BETWEEN”);