Calculate no of Business days between two dates

building reports desktopintelligence
1

Hi -

Im trying to get the difference between two dates but I want to know the number of BUSINESS days and not just the absolute number of days.

I have used the following formula so far:

=DaysBetween( ,)

is there any trick I can use to get BO to tell me the number of Work days elapsed?

Thanks
Mandy

Maximo625 (BOB member since 2006-04-26)

2

I don’t think there is an easy way to do it with BO function. You should try to calculate it at database level, store it as view and query it with BO

bernard timbal :fr: (BOB member since 2003-05-26)

3

I was half expecting that answer…

Thanks anyway!
Mandy

Maximo625 (BOB member since 2006-04-26)

4

If you need to calculate it on the fly and are using an Oracle back end, you can use a combination of the Lag function and your calendar table to do it. You will need to decide if you include holidays or not. If you wish to exclude them the SQL gets a bit more complex and you have to have them marked in your calendar table.

  • Ernie

eepjr24 :us: (BOB member since 2005-09-16)

5

Hi Mandy,

I knocked together this function a while ago for transact to do just what you are after;

CREATE FUNCTION dbo.workdays_between(@from_date DATETIME, @to_date DATETIME)
RETURNS DECIMAL(9,0)
AS
BEGIN
DECLARE @v_days INTEGER,
@progress_date DATETIME,
@start_date DATETIME,
@end_date DATETIME

/*Check which day comes first*/
IF @from_date > @end_date
	BEGIN
		SET @start_date = @to_date
		SET @end_date = @from_date
	END
ELSE
	BEGIN
		SET @start_date = @from_date
		SET @end_date = @to_date
	END
	
SET @v_days = 0
SET @progress_date = @start_date


/*loop from first days to last, if days is weekday add one else add zero*/

WHILE @progress_date <= @end_date

	
	BEGIN
	SET @v_days = @v_days + (SELECT CASE DATEPART(WEEKDAY,@progress_date)
					WHEN 1 THEN 0
					WHEN 7 THEN 0
					ELSE 1
				   END)
	
	SELECT @progress_date = DATEADD(dd,1,@progress_date)
	END


RETURN @v_days

END

It is inclusive and does not take account of corporate holidays (that would require a calendar table.

Shouldn’t be to difficult to convert for oracle (use a cursor rather than the select case that Ihave used).

jeffH :uk: (BOB member since 2006-03-31)

6

Dear

If you use Oracle and you can consider only fixed holidays (sat & sun)

yes you can use the DayOfWeek function
and then make a measure object not including sat & sun
it is the same way of the script but it is directly from BO

Greetings from Rome

Rob

baronero :it: (BOB member since 2006-02-24)

7

Hi all

thanks for all your advice, I dont have much access for making changes to the actual database so I tried to use the “Date Functions” - mainly using the ‘W :slight_smile: :slight_smile: :slight_smile: eek’ and ‘DayNumberOfWeek’ functions.

I calculated the DayNumber for each of the two dates (e.g. 1 for monday, 2 for tues etc) and the week number for each of the two dates.

From that I obtained the week diff (the number of whole weeks between the two dates)

After a bit of trial and error (!) I then applied the following formula to calculate the number of business days elapsed:

=(<day number (engine date)>-<Day number (trade date)>)+((*5)+1)

I tested my results in excel with NetWorkDays function and it seemed to work fine…

Of course this currently only works as long as the two dates are in the same year (which mine greatfully are) but I think the formula could be amended to take years into account quite easily…

It doesn’t take into account bank holidays but Im happy to live with that one!!

Thanks again!
Mandy

Maximo625 (BOB member since 2006-04-26)

8

Hi everyone,

Has anyone tried using a business calendar table in oracle to calculate between two dates? and apply an object that calculates only business days in the universe level?

thanks,
Antzcp04

antzcp04 (BOB member since 2006-08-04)

9

I’ve been doing quite a lot of work around this in the last few weeks, coincidentally, and the best solution is generally to go to DB level.

Particularly this is in regard to national holidays, because they are locale dependant.

Damocles :uk: (BOB member since 2006-10-05)

10

Hi Damocles,

Thanks for your reply, Do you have any sample approach as to how can you make a business day lookup for objects of the universe. I have created a physical table in oracle but still figuring out my approach on getting business days between2 dates at universe level…

thanks,
antzcp04

antzcp04 (BOB member since 2006-08-04)

11

We have implemented this using our calendar dimension table. In the end, the universe just needs a basic SQL to handle this. Something like:

Select count(*)
from tbl_Dimn_Caln
where
F_Week_Day = ‘Y’ and
F_Hold = ‘N’ and
D_Perd between StartDate and EndDate

F_WeekDay is our Day of Week flag.
F_Hold is our holidaay flag.
D_Perd is the date (period date).

  • Ernie

eepjr24 :us: (BOB member since 2005-09-16)

12

Hi Ernie,

Thanks for the info.

regards,
Antzcp04

antzcp04 (BOB member since 2006-08-04)