ABAP CDS view: distance in working days between two dates

Dear community!

Recently I was asked to suggest solution for ABAP CDS-view which should demostrate distance in working days between two dates (begin_date and end_date) of workflow steps. The CDS-view should be used in SAP Smart Business framework to present data for KPI visualisation.

I found interesting blog regarding the factory calendar consumption in HANA calculation view. But I need ABAP-CDS because of SAP Smart Business framework: it’s easy to generate OData-service from CDS-view to be further consumed by the framework.

Let’s say I have table with stepid, date_start and date_end fields. And I need to get distance in workdays (according local factory calendar). Result should be like this:

Two different approaches I can suggest:

1. Create ABAP-CDS view based on AMDP-procedure with SQL-SELECT inside. The SELECT uses basic data table and built-in SQL-function WORKDAYS_BETWEEN to be applied to date_start and date_end.
2. Create ABAP-CDS view based on basic data table joined with “distance table”. “Distance table” contains workday distances for any combinations of dates in particular range, i.e. for any dates between 01.01.2017 and 31.12.2018. The table is filled once by custom ABAP-report and will contain less than 200000 records for 2 years. Not many for SAP HANA.

“Distance table” can be like this:

And its sample data:

As you can see, according RU factory calendar there is the long holiday period in the beginning of the year!

ABAP-CDS view can be like this…

.. and returns resultset as showed in the 1st screenshot in the blog.

Approach 2 seems to be better (from my point of view): it’s more flexible. You can use the “distance table” anyway, both in CDS-views or just open SQL.. And I believe it’s more fast that approach with WORKDAYS_BETWEEN function. But I didn’t conduct performance tests…