Skip to Content
Author's profile photo Former Member

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

Assigned tags

      5 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo John Appleby
      John Appleby

      As of HANA SPS07, you can just use WORKDAYS_BETWEEN:

      WORKDAYS_BETWEEN - SQL Reference - SAP Library

      John

      Author's profile photo Rama Shankar
      Rama Shankar

      Thanks John! 🙂

      Author's profile photo Former Member
      Former Member

      WORKDAYS_BETWEEN is not working for graphical views, What is the option ?

      Author's profile photo Eduardo Savoine
      Eduardo Savoine

      Work with script calcution view

       

      Author's profile photo Ramana Ramana
      Ramana Ramana

      Hi Goutham,

      Thanks for the detailed post. I have come up with same scenario, but I have to find the hours between two dates by excluding weekends. Could you please help me with code, if you have time. Thanks in advance.

      The above code is giving correct values for me.

       

      Thanks,

      Ramana.