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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Subject | Kudos |
---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
User | Count |
---|---|
6 | |
6 | |
5 | |
4 | |
4 | |
3 | |
3 | |
3 | |
3 | |
3 |