Day Difference in OBIEE 11g excluding weekends

Question

Hi All,

I am trying to find out the the day difference between two dates excluding the sat and sunday but I'm not getting it working. I've tried the following code below, but it not worked well :

DAYOFWEEK("Date_1")-DAYOFWEEK("Date_2")-4+

(TIMESTAMPDIFF(SQL_TSI_DAY,

TIMESTAMPADD( SQL_TSI_DAY , 1 - DAYOFWEEK("Date_1"), "Date_2"),

TIMESTAMPADD( SQL_TSI_DAY , 8 - DAYOFWEEK("Date_2"), "Date_2")))/7*5)

Does anyone knows how to calculate that ?

Thanks in advance.

Regards

[Deleted User] · Answer

https://community.oracle.com/tech/apps-infra/discussion/comment/16807553#Comment_16807553

a) This is a 2-year-old thread, please don't resurrect zombies.

b) As stated 2 years ago the notion of "weekend" is an artificial one and can change from country to country and hence...

c) ...must be tied to the actual data. you can't write a formula that will be correct in every case.

d) A pure formula is the least performant option since this logic will have to be interpreted for every single row that gets parsed.

User_7KAH8 · Answer

How about: date2 - date1 - TIMESTAMPDIFF(SQL_TSI_WEEK,date1,date2)*2 ?

Joel Acha · Answer

Trying to implement this logic in code is a nightmare. What about leap years for example? As @Christian Berg said above, the best solution and best practice is to have a date dimension in which you'd have a flag that indicates if a date is a weekday in your case.