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

As of HANA SPS07, you can just use WORKDAYS_BETWEEN:

WORKDAYS_BETWEEN – SQL Reference – SAP Library

John

Thanks John! ðŸ™‚

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

Work with script calcution view

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.