Network Days or Business Days Calculation

DiDio

Summary: Looking for advice on how to calculate network/business days.

In excel it's super easy to calculate business days / holidays by using the networkdays function, in oracle not so much. Does anyone know how to calculate

Content (required):

Version (include the version you are using, if applicable):

Code Snippet (add any code snippets that support your topic, if applicable):

ALOK SH-Oracle

Can you please elaborate. Are you looking for any predefined function or any custom formula you want to build.

It is for OAC classics or DV or any other tool ?

Gianni Ceresa

Give me a simple universal definition of a "business day" and I give you a simple function for it.

Every single business has a personal definition of "business day": different countries work different days, same country but different fields work different days, and it's better to not even talk about holidays (I'm in Switzerland: there are more than 26 different calendars inside the country for holidays, and we are really small, how could a generic function even be able to handle that?). Or what about the support for a different business model that start being tested and practiced like the 4-days work week? Excel will have not a single chance to calculate that correctly, they have a parameter for holidays because they know they can't get it automatically, but still they expect everybody to work 5 days in a 7 days week. Even just that is a business rule: your company maybe work 5 days per week, your neighbor work 4 days per week, the next one 6 days per week.

The solution is what has always been the solution for these needs or also the needs for fiscal calendars support in analytics: have your own full and well defined time dimension.

Provide your own time dimension containing all the details about your own meaning of "business day" and holiday (for every day you have multiple attributes marking it as work day or not, as holiday or not etc.), from here you can easily calculate all you want taking into consideration work days, holidays etc.

Michal Zima

Completely agree with @Gianni Ceresa - this should be "handled" in data - thus designing (and populating) your time dimension in database as per your business requirements. And then every BI/Data visualization tool can benefit from it.

DiDio

@Gianni Ceresa @Michal Zima and everyone thank you for the kind responses. So by business days I mean in the U.S and simply put for now I'd like to exclude Sat/Sun from the calculation.

So if i have a date of 6/7/2022 and another date of 6/15/2022 the count of days should be 7 days because i'm excluding sat/sun.

Keeping it as simple as that for now. Holidays are another scenario and not as important to the calculation.

This would be a newly created column in the data within OAC.

Gianni Ceresa

As there isn't a function doing that in one step, you need to get there by some logical operations. There is a function calculating the number of days between 2 dates, that same function can give you also the number of weeks between 2 dates (or you can take the days / 7). You then only need to adjust things a bit to cover the cases of adding or removing a few days depending on the 2 dates itself.

It is really a full expression that you will need in your formula: break it down into pieces adding the required extra bit to get closer and closer to the exact result.

Or, as said, implement a proper time dimension...