Skip to Content

Hi All,

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.

temp.PNG

Company Leave Calendar for Jan 2016:


temp.PNG

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 🙂 .

temp.PNG

Procedure Snippets:

[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;

      ELSE

        V_BUSINESS_DAYS_COUNT := V_TOTAL_DAYS_COUNT – V_HOLIDAYS_COUNT;

      END IF;

          

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;

END FOR;

OUTPUT_TABLE = UNNEST (:ARR_START_DATE, :ARR_END_DATE, :ARR_BUSINESS_DAYS_COUNT)

                AS (“START_DATE”, “END_DATE”, “WORKDAYS_BETWEEN”);

END

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply