OBIEE: Date Difference Excluding Weekends

We need to calculate the difference between two dates excluding weekends. Here we have considered Friday and Saturday as weekends.

We can create a function in database which can be used to do the same job.

 CREATE OR REPLACE Function WORKINGDAY( DATE1 IN DATE,DATE2 IN DATE )
   RETURN number
IS
   workingday number;
 
BEGIN

  select (CASE WHEN (COUNT(ROW_WID)-1)<0 then COUNT(ROW_WID) else COUNT(ROW_WID)-1 end) INTO workingday
 from w_day_d where   CALENDAR_DATE between DATE1 and DATE2
 AND  DAY_NAME NOT IN ('Friday','Saturday');

RETURN workingday;
END;

We can call this PL/SQL function in OBIEE report.

EVALUATE('WORKINGDAY(%1,%2)' as double, DATE1,DATE2)


Stay well and enjoy !!