Categories
- All Categories
- Oracle Analytics and AI Learning Hub
- 51 Oracle Analytics and AI Sharing Center
- 19 Oracle Analytics and AI Lounge
- 288 Oracle Analytics and AI News
- 57 Oracle Analytics and AI Videos
- 16.3K Oracle Analytics and AI Forums
- 6.5K Oracle Analytics and AI Labs
- Oracle Analytics and AI User Groups
- 111 Oracle Analytics and AI Trainings
- 21 Oracle Analytics and AI Challenge
- Find Partners
- For Partners
Day Difference in OBIEE 11g excluding weekends
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
Answers
-
If you have a correctly formed time dimension, then you will not need any weird code.
In the time dimension table you store weekday / weekend information and can easily select the requested days. DB operations are a lot more performant than code logic.
0 -
Please post some example about what information you have and what is the expected output using xls or OBIEE screens.
0 -
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.
0 -
How about: date2 - date1 - TIMESTAMPDIFF(SQL_TSI_WEEK,date1,date2)*2 ?
0 -
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.
0
