This discussion is archived
8 Replies Latest reply: Sep 2, 2013 6:01 AM by 8a549bdb-64dd-4f34-ad9e-68de4893ae93 RSS

Count Working Days

8a549bdb-64dd-4f34-ad9e-68de4893ae93 Newbie
Currently Being Moderated

Hey huys,

 

I have a problem with the count of working days in the report I am developing.

 

I make the count of days with TIMESTAMPDIFF but this return all days between  Start Date and end date and I Want only working days. I have a column that says if the day is a work day or not.

 

I don´t have any idea how I make the comparison day by day.

Can any suggest how I do it.

 

Cumpz

  • 1. Re: Count Working Days
    SrikanthGH Newbie
    Currently Being Moderated

    I have created a new post for the above issue, as I couldn't post my suggestion here for some reason...

     

    OBIEE - Just Blog It: Number of Working Days between Two Days - Column "Work Day" with flag "Yes" o…

     

    Hope its helpful

     

    ------

    Srikanth

  • 2. Re: Count Working Days
    8a549bdb-64dd-4f34-ad9e-68de4893ae93 Newbie
    Currently Being Moderated

    Thank you for your answer, but I had seen this explanation but does not help in my case because I have a column that tells me if the day is a work day or not, and have to do it in answer.

     

    Example:  Start Date = 2013-09-02

                     End Date =  2013-09-30

    If 2013-09-11 and 2013-09-19 are holidays, my column to have return 18 days.

     

    I have a column with start date of campaign, other with end date and the column "Work Day" whith flag "Yes" or "NO".

     

    Cumpz

  • 3. Re: Count Working Days
    JamalSaleh Newbie
    Currently Being Moderated

    If you have "Working Day" flag why don't you use filter to exclude "N" then simply count

  • 4. Re: Count Working Days
    SrikanthGH Newbie
    Currently Being Moderated

    We can try to create a function in DB which returns the Number of Working Days on passing the Start Date and End Date of Campaign Calendar

    By you saying there is a column "Work Day" with flag "Yes" or "NO", I assume there is a table called Campaign Calendar which may provide this info like if each and every day is working day or not.

    For ex:

    Work_Day                  Flag

    02-SEP-13 00:00:00    YES

    03-SEP-13 00:00:00    YES

    04-SEP-13 00:00:00    YES

    05-SEP-13 00:00:00    YES

    06-SEP-13 00:00:00    YES

    07-SEP-13 00:00:00    NO

    08-SEP-13 00:00:00    NO

    If the above is the case, then try to create a function  as below which return number of working days between two dates

     

    CREATE

    FUNCTION DATE_DIFF(ST DATE, ED DATE) RETURN NUMBER

    IS NO_OF_WDAYS NUMBER;

    DT1 DATE := ST;  ---VARIABLE TO HOLD START DATE

    CHR1 VARCHAR2(10);   ----VARIABLE TO HOLD FLAG FOR EACH DATE

    HOLIDAY NUMBER := 0;  ----COUNTER WHICH GIVES US NUMBER OF HOLIDAYS BETWEEN TWO DATES INCLUDING SATURDAYS AND SUNDAYS

    BEGIN

      NO_OF_WDAYS := TRUNC(ED - ST);

      LOOP

      SELECT Work_Day /* THE COLUMN WHICH HOLDS THE FLAG*/

      INTO CHR1

      FROM CAMPAIGN_CALENDAR /* THE TABLE WHICH HAS THE INFO */

      WHERE DATE_COLUMN = DT1;

        IF(CHR1 = 'NO') THEN

        HOLIDAY := HOLIDAY + 1;

        END IF;

      DT1 := DT1 + 1;

      EXIT WHEN DT1 > ED;

      END LOOP;

      NO_OF_WDAYS := NO_OF_WDAYS - HOLIDAY;

      RETURN NO_OF_WDAYS;

    END DATE_DIFF;

     

    Once the above function is written, use Evaluate function in the RPD and pass both the columns as parameters.

    EVALUATE('DATE_DIFF(%1,%2)' ,Campaign Start Date,Campaign End Date)

    Whenever the column gets pulled in the report, the above db function will be fired hence resulting in Number of Working days.

    Hope its helpful

    -----

    Srikanth

  • 5. Re: Count Working Days
    8a549bdb-64dd-4f34-ad9e-68de4893ae93 Newbie
    Currently Being Moderated

    Hey,

     

    Thank for your answer, I will test,

     

    Cumpz

  • 6. Re: Count Working Days
    8a549bdb-64dd-4f34-ad9e-68de4893ae93 Newbie
    Currently Being Moderated

    I think it´s Ok, but I get this error, --> A general error has occurred. [nQSError: 43113] Message returned from OBIS. EVALUATE_SUPPORT_LEVEL inside NQSConfig.INI is not set to support EVALUATE. (HY000)

     

    Thank you very much, I will go configure the file and test again.

     

    I use this function somewhat simpler:

     

    create or replace

    FUNCTION DIF_days

    (

      startdate IN NUMBER 

    , enddateIN NUMBER

    ) RETURN NUMBER AS

     

      diff number;

      aux number;

      aux2 number;

      work_day varchar2(1);

     

    BEGIN

     

     

      diff:= 0;

      select count(*) into diff from dim_time

        where id_date >= startDate

          and id_date < endDate

          and flag_Work = 'Y';

     

      RETURN diff

     

    END DIF_days

     

     

    Cumpz

  • 7. Re: Count Working Days
    SrikanthGH Newbie
    Currently Being Moderated

    The error: A general error has occurred. [nQSError: 43113] Message returned from OBIS. EVALUATE_SUPPORT_LEVEL inside NQSConfig.INI is not set to support EVALUATE. (HY000)


    It is because the Evaluate parameter in NQSConfig.ini file wasn't setup appropriately.

    Please go to the following path:

    OBIEE_INSTANCE/config/OracleBIServerComponent/coreapplication_obisn


    Open the NQSConfig.INI file in Edit mode

     

    Find the following lines:

    # EVALUATE_SUPPORT_LEVEL:

    # 1: evaluate is supported for users with manageRepositories permssion

    # 2: evaluate is supported for any user.

    # other: evaluate is not supported if the value is anything else.

    EVALUATE_SUPPORT_LEVEL = 0;


    Change the line as
    EVALUATE_SUPPORT_LEVEL = 2;


    Restart your BI Services


    Mark if it is helpful

     

    -----

    Srikanth

  • 8. Re: Count Working Days
    8a549bdb-64dd-4f34-ad9e-68de4893ae93 Newbie
    Currently Being Moderated

    Thank you. I wil do this.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points