Calculating Working Days

[AliB007]

I need to be able to return the working days between 2 dates. I know of the daysbetween function but this is of no use when the time difference spans over a weekend. Does anyone know of a script / mathamatical formula to work this out? Also does BO v.4.1.4/4.1.6 support PL/SQL?
Thanks in advance
A

 

[BobWard]

You will need to introduce a calendar table to hold this information. The simpliest of tables would be 2 columns Date, IsWorkingDay. You can then join your fact table to the date column in this new table. You can expand this table to do many things includng hold date ranges fro lat month, last tear etc.

 

[AliB007]

Thanks For the info, In case anyone else is interested here's what I've used :

select (( to_number(trunc(END DATE,'D') - trunc(START DATE +6, 'D')))/ 7*5) +
mod(7 - to_number(to_char(START DATE, 'D')),6) +
least(to_number(to_char(END DATE, 'D')) - 2,5) from TABLE NAME

Substitute START DATE and END DATE with your 2 fields


Seems to work OK, Depends on your definition of how many days between 2 dates (if you count start dates etc.)

 

[BobWard]

What about Bank Holdays etc..

 

[AliB007]

Fair Comment, Your soloution, which I also had from the Bus Obj help desk is the only way to take into account holdidays/Bank holidays etc...