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

    Count Working Days

    8a549bdb-64dd-4f34-ad9e-68de4893ae93

      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

          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

            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
              Jamal Saleh

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

              • 4. Re: Count Working Days
                SrikanthGH

                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

                  Hey,

                   

                  Thank for your answer, I will test,

                   

                  Cumpz

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

                    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

                      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