Business Days Calculations in BOE XI

[lnordstrom]

Hello,

I need to be able to calculate business days in some of my date math formula. Does anyone know if there is an easy way to do this?

Thanks,
Lisa

 

[hilfy]

There's no simple way to just calculate this if you need to account for holidays. Instead, I use a separate table called Business_Day_Calendar to set up for business day calculations. The table has the following fields:

Calendar_Date - datetime
Sequence - integer
Weekend - bit
Holiday - bit

The Sequence is the most important field here - it increments for every business day and stays the same for weekends and holidays. Here's an example of how the data looks:

[u]Calendar_Date[/u]     [u]Sequence[/u]     [u]Weekend[/u]     [u]Holiday[/u]
09/15/2010           250          0           0
09/16/2010           251          0           0
09/17/2010           252          0           0
09/18/2010           252          0           0
09/19/2010           252          0           0
09/20/2010           253          0           0

I then create a simple database function that has two date parameters and returns the number of business days between them. The MS SQL Server version of the function returns the result of the following SQL:

Select Coalesce(bdcTo.toDay - bdcFrom.fromDay + 1, 0)
    from
     (Select Business_day_Sequence fromDay
      from Business_Day_Calendar 
      where Calendar_Date = Cast(Convert(varchar, @FromDate, 101) as datetime)) bdcFrom,
     (Select Business_Day_Sequence toDay
      from Business_day_Calendar
      where Calendar_Date = Cast(Convert(varchar, @ToDate, 101) as datetime)) bdcTo

If you can add something like this to your database, it will make this calculation MUCH easier. There are other ways of doing this, but this is one of the most efficient that I've found.

-Dell



A computer only does what you actually told it to do - not what you thought you told it to do.