Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Day Difference in OBIEE 11g excluding weekends

Received Response
263
Views
5
Comments
user11048445
user11048445 Rank 1 - Community Starter
edited Aug 13, 2024 5:13PM in Oracle Analytics Cloud and Server

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

  • [Deleted User]
    [Deleted User] Rank 2 - Community Beginner

    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.

  • SonPat99
    SonPat99 Rank 6 - Analytics Lead

    Please post some example about what information you have and what is the expected output using xls or OBIEE screens.

  • Joel
    Joel Rank 8 - Analytics Strategist

    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.

  • User_7KAH8
    User_7KAH8 Rank 1 - Community Starter

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

  • [Deleted User]
    [Deleted User] Rank 5 - Community Champion

    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.