caculate business days

[Scottpuck]

Hello everyone... I am new to Cognos and am needing a little help. I am trying to figure out a way to create a calculate the number of business days (M-F)that have lapsed from a given date to the current date. Has anyone any ideas to help me out on this one???

Thanks in advance,

Scott

 

[griffindm]

I don't see a function available for this. You could probably write a database function and include it in Impromtpu. Another alternative would be to determine the number of weeks between the two dates and work out the business days from that (knowing the day of week of the two end dates).

HTH,

Dave Griffin

 

[angiole]

In Oracle-speak, the following will count occurances of Monday to Friday dates ...

You must retrieve the day number component of a date then ...

select
SUM(
DECODE(to_char(date,'D'),1,0,7,0,1))
AS day_number
from table_A

The function decode will return a 1 for Monday to Friday day numbers, else a 0, so in effect you will only sum Mondays to Fridays

Cheers

 

[Scottpuck]

Thanks everyone... I will give these a try

 

[gasman009]

If you are working with a lot of business-days calculations in a relatively small time-span, another idea is to use Excel to generate a Hotfile with every date in that time span (i.e. Fiscal Year 2001 - 2002). Tie that Hotfile into the Report and use Impromptu's DayOfWeek Function to count if Dates in 2,3,4,5,6 (where 2= Monday). Conversely, you can use Excel's DayOfWeek function as another column in the Excel file.

(This is something I thought up in retrospect to another report I was building, and therefore never tried it out yet.)

 

[SomerG]

I'm a newbie and have been playing around with impromptu trying to get
useable info. from our complicated database. Here's what I came up with to exclude Saturdays & Sundays in order to calculate the number of business days between two dates:

((week(ending date)-week(beginning date))*5) - (dayofweek(beginning date) -
dayofweek(ending date))

Now I'm trying to figure out an "easy" way to include holidays in the equation.
Does anyone have an idea?

gary