How to calculate Number of Business Days in Web Intelligence

building reports webintelligence
1

Hello All,

I guess there is no direct formula to calculate Number of Business days in Web Intelligence XI R2.

 Is it possible to calculate number of Business days in Web Intelligence

Ex: ( It is some thing like DaysBetween() should exclude Holidays, while calculating days between two dates)

Thank you
Keerthana

Keerthana (BOB member since 2008-01-11)

2

If you need a calculation that excludes holidays you really need to do it with a calendar table in your database. Quite frequently people (companies) have different holidays, so you need to be able to set up your own.

Ignoring the holiday question, try this:

Dave Rathbun :us: (BOB member since 2002-06-06)

3

Ignoring the holiday question, I just wrote a UDF for a kick that excludes weekends in calculating # of days.

It may not be very impressive but here is the code:

[i]
create function dbo.fCountNoOfBusDays
(@bDate smalldatetime, @eDate smallDatetime)
returns int
as
begin
declare @loopCntr int
set @loopCntr = 0

while @bDate <= @eDate
begin
	if DATEPART(WEEKDAY, @bDate) not in (6,7)
		set @loopCntr = @loopCntr + 1 

	set @bDate = dateadd(day,1,@bDate)
end
return @loopCntr

end

select dBeginDate, dEndDate, dbo.fCountNoOfBusDays(dBeginDate, dEndDate)
from
[/i]

sunjer (BOB member since 2008-01-30)

4

That would work in a universe, but not in a report.

Dave Rathbun :us: (BOB member since 2002-06-06)

5

it should :slight_smile:

the SQL for # of days will be:

dbo.fCountNoOfBusDays(dBeginDate, dEndDate)

sunjer (BOB member since 2008-01-30)

6

Thank you all for your input on this query. I was busy with other issues at work. I will try to follow your approaches to calculate the difference between days.

Thank you once again
keerthana

Keerthana (BOB member since 2008-01-11)