Scenario Where a Graphical Calculation View will Not Suffice

In my last HANA project, I had a requirement to build a HANA model for the supply chain delivery performance report.

I was able to incorporate most of the business logic required for the report in the graphical calculation view but there was requirement where I had to incorporate logic to calculate the number of working days between two specific dates excluding weekends ,for which ,a graphical calculation view wouldn’t suffice .

For this requirement, I had to build a scripted based calculation view as a wrapper on top of the graphical calculation view to incorporate the required logic.

The below SQL snippet is written in the the scripted based calculation view  to calculate the number of working days between the actual and promise ship dates excluding weekends.

(SELECT CAST((ABS(DAYS_BETWEEN(TO_DATE(ACTUAL_SHIP_DATE),TO_DATE(PROMISE_SHIP_DATE))) / 7) AS INTEGER) * 5 +

  MOD(ABS(DAYS_BETWEEN(TO_DATE(ACTUAL_SHIP_DATE), TO_DATE(PROMISE_SHIP_DATE))), 7) – (

        SELECT COUNT(*)  FROM (

SELECT 1 AS d FROM DUMMY UNION ALL

SELECT 2 FROM DUMMY UNION ALL

SELECT 3 FROM DUMMY UNION ALL

SELECT 4 FROM DUMMY UNION ALL

SELECT 5 FROM DUMMY UNION ALL

SELECT 6 FROM DUMMY UNION ALL

SELECT 7 FROM DUMMY

) AS weekdays

        WHERE

       d < MOD(ABS(DAYS_BETWEEN(TO_DATE(ACTUAL_SHIP_DATE),TO_DATE(PROMISE_SHIP_DATE))), 7) AND

DAYNAME(ADD_DAYS(GREATEST(TO_DATE(ACTUAL_SHIP_DATE), TO_DATE(PROMISE_SHIP_DATE)), -d)) IN (‘SATURDAY’, ‘SUNDAY’)) FROM DUMMY) as PROM_ACT_SHIP_DAYS

Questions/Comments are welcome.

– Goutham