Calculate Business Days between two Dates Oracle Rightnow

Question

I am working in Oracle RightNow and I was using the below to calculate the Workdays between two dates but with the new year, the logic seems to no longer work. This would be the code used in a custom report column for a client.

((DATE_DIFF(sysdate(),incidents.created)/86400) + 1) - ((to_number(date_format(sysdate(),'WW')) - to_number(date_format(incidents.created,'WW')))* 2) - IF(Date_format(incidents.created,'DAY') = 'Sunday',1,0) - IF(Date_format(sysdate(),'DAY') = 'Saturday',1,0)

Is there different code I could use to accomplish this without it breaking with a new new year?

Thanks, B

Answer 1

You can use this Oracle answer:

SELECT ( TRUNC( SYSDATE, 'IW' ) - TRUNC( created, 'IW' ) ) * 5 / 7
       + LEAST( SYSDATE - TRUNC( SYSDATE, 'IW' ) + 1, 5 )
       - LEAST( created - TRUNC( created, 'IW' ) + 1, 5 )
          AS WeekDaysDifference
FROM   incidents;

Which, for the sample data:

CREATE TABLE incidents ( created ) AS
SELECT DATE '2020-12-31' FROM DUAL UNION ALL
SELECT DATE '2021-01-01' FROM DUAL UNION ALL
SELECT SYSDATE FROM DUAL

Outputs:

|                       WEEKDAYSDIFFERENCE |
| ---------------------------------------: |
| 2.83034722222222222222222222222222222222 |
| 1.83034722222222222222222222222222222222 |
|                                        0 |

db<>fiddle here

Looking at the Oracle RightNow CRM documentation, you can convert the Oracle solution to:

DATE_DIFF(
  DATE_TRUNC( SYSDATE(), 'IWEEKS' ),
  DATE_TRUNC( incidents.created, 'IWEEKS' )
) / 86400 * 5 / 7
+
IF (
  DATE_DIFF(SYSDATE(), DATE_TRUNC(SYSDATE(), 'IWEEKS')) / 86400 < 5,
  DATE_DIFF(SYSDATE(), DATE_TRUNC(SYSDATE(), 'IWEEKS')) / 86400,
  5
)
-
IF (
  DATE_DIFF(incidents.created, DATE_TRUNC(incidents.created, 'IWEEKS')) / 86400 < 5,
  DATE_DIFF(incidents.created, DATE_TRUNC(incidents.created, 'IWEEKS')) / 86400,
  5
)

(Note: I do not have access to an Oracle RightNow environment so the conversion is untested and based solely on the documentation and there may be errors or possible improvements; however, it is hopefully sufficient to give you the solution.)

Answer 2

Oracle B2C Service (F.K.A. RightNow) doesn't use Oracle DB currently. And, the syntax for the analytics tool is unique to the application, not the underlying DB. So, the suggestions regarding a SQL-based solution won't work.

Your logic appears to be impacted by your use of the transformation of dates using WW in the date_format function, which is the week number for the date in the first parameter of the function. This resets to 1 at the new year, which is why you're likely getting the behavior that you are; you're not accounting for the significance of multiple years in the equation.

Does the organization that you built the report for have working hours setup in B2C Service? If so, then you might be able to simplify your formula with rel_date_diff, which might handle some of the weekday/weekend logic for you. You could try to "brute force" the equation with a date_diff, then calculate the weeks of the diff, then multiply that times 5 workdays per week. But, you'll get anomalies based on what you consider to be the first day of the week and when the report is run. Use the documentation to find appropriate functions to help you.

The last option, but one that will work in the event you cannot develop an equation using the out-of-the-box functions in analytics, is to build an analytics report extension. Your custom code for the extension could implement the date processing logic however you require (in C# for the .NET console and JavaScript for the BUI -- hopefully your client has moved to BUI).