One of my users wants to calculate the number of working days between two dates. We are running business objects 4.1.3 against an Oracle 7.3 data warehouse.
Any suggestions/assistance would be greatly appreciated.
Regards
Tom Wright
IT Project Manager
Manchester City Council
Tom_Wright@notes.manchester.gov.uk
Listserv Archives (BOB member since 2002-06-25)
Hi Tom
For e.g date1=09/18/98 & date2=09/21/98
working days = (date2-date1)+1 = 4. This is the formula. The query is
Select date(date2)-date(date1)+1 from table
I hope this will help you.
Ravi.
______________________________________________________ Get Your Private, Free Email at http://www.hotmail.com
Listserv Archives (BOB member since 2002-06-25)
We did this for our users. We built an Oracle function that takes in the begin date and the end date, and returns the number of week days between the two dates. It does not take into consideration any holidays. It simply counts the weekdays. Then we built some pre-defined objects into the universe using this function. The only downfall is that the Designer needs to create objects in the unviverse, the user cannot create the objects on their own.
The other thing that we have not yet tried with this function is to add a function into the ora7en.prm file. If you can get this to work, then the user can use the Oracle function to create their own objects on reports.
The function is attached.
Hope this helps!
Amy Martel
amartel@allmerica.com
Tom Wright tom@WRIGHT.CLARANET.CO.UK 09/19/1998 12:23pm >>>
One of my users wants to calculate the number of working days between two
dates.
–=_03542201.86E7844B
Content-Disposition: attachment; filename=“C#BUSDYS.SQL”
–calculates number of business days between two dates --input: beg_dt (date field)
– end_dt (date field)
–output: num_days (numeric field in days)
drop function c#busdys;
create or replace FUNCTION c#busdys (beg_dt in date, end_dt in date) RETURN NUMBER
is
cdy char(3);
num_days number(7,2);
begin
cdy := to_char(beg_dt, ‘DY’);
num_days := floor(trunc(end_dt) - trunc(beg_dt)); if cdy = ‘MON’ then
num_days := floor(num_days / 7);
elsif cdy = ‘TUE’ then
num_days := floor((num_days + 1) / 7);
elsif cdy = ‘WED’ then
num_days := floor((num_days + 2) / 7);
elsif cdy = ‘THU’ then
num_days := floor((num_days + 3) / 7);
elsif cdy = ‘FRI’ then
num_days := floor((num_days + 4) / 7);
elsif cdy = ‘SAT’ then
num_days := floor(num_days / 7);
elsif cdy = ‘SUN’ then
num_days := floor(num_days / 7);
else
num_days := 0;
end if;
num_days := trunc((end_dt - beg_dt) - (2 * num_days),2); return num_days;
end;
/
grant execute on c#busdys to public;
drop public synonym c#busdys;
create public synonym c#busdys for c#busdys;
–=_03542201.86E7844B–
Listserv Archives (BOB member since 2002-06-25)