Skip to Content

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

To report this post you need to login first.

5 Comments

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

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

    (0) 

Leave a Reply