Optimized Calculation Algorithm for Business Days

• Download source files - 4.58 Kb
• Download demo project - 9.5 Kb

Introduction

My teammate came to me and asked, how to figure out the number of business days between two given dates? My immediate reply was, loop through start date to end date. But after some time, I realized that this solution is not optimized. So I started developing my own optimized algorithm.

In this article we will discuss the algorithm and see the implementation on SQL Server 2000 and also some C# code.

Let’s start with algorithms first. Here are the key factors that are involved in the calculation.

The factors

1. How many week days per week?

   This depends on the company/country policies. In the US, it's generally 5 days per week, whereas in India and in many other countries, many companies work for 6 days a week. Our algorithm needs to take that into consideration.

2. How many holidays?

   The second factor is the number of holidays during a specified period. Again that depends on company policy. We can maintain these details in one table. That is simple too.

Basic algorithm

1. Calculate the number of time span in terms of weeks. Call it, W.
2. Deduct the first week from the number of weeks. W= W-1
3. Multiply the number of weeks with the number of working days per week. Call it, D.
4. Find out the holidays during the specified time span. Call it, H.
5. Calculate the days in the first week. Call it, SD.
6. Calculate the days in the last week. Call it, ED.
7. Sum up all the days. BD = D + SD + ED – H.

Simple enough hum…!!!

As we know the number of working days per week, and as the pattern repeats every week, first we will calculate the number of complete weeks. We should deduct one from the number of weeks. Once we get the number of weeks, we can then multiply by the number of working days and arrive at a rough number of business days.

OK, so we have a rough number of business days, say D. I am calling it ruff number as it is not the exact answer, as we have deducted one week in the first step.

Now, we will find out how many days are there in the starting week. For example if the starting day is Wednesday, the number of days in the starting week is 3 (if number of business days in a week is 5) or 4 (if number of business days in a week is 6).

Similarly, find out how many days are there in the last week. At last, find out how many holidays fall during that time span. That will be a simple aggregation query on the Holiday table.

Once we have all the numbers in hand, based on the mentioned expression, we can come up with our desired answer. Hope this clears all your doubts. So let’s start implementing this algorithm.

First, we will try to implement this on SQL Server using T-SQL.

SQL Server implementation

SQL Server has a number of date functions, two of them are DATEDIFF and DATEPART. That will come handy in this implementation.

You can implement this algorithm as a procedure or a function. The preferred one is function but here I will implement this as a procedure so that we can use the print command. Once everything is OK, you can convert it to a procedure by just removing the print command and using return.

The solution

Create procedure SpBusinessDays (@dtStartDate datetime, @dtEndDate datetime,
@indDaysInWeek int)
as
begin
    declare 
    @intWeeks int
    ,@indDays int 
    ,@intSdays int
    ,@intEdays int
  
    -- Find the number of weeks between the dates. Subtract 1 
    -- since we do not want to count the current week.
  
    select @intWeeks = datediff( week, @dtStartDate, @dtEndDate) - 1
    print 'week'
    print @intWeeks
  
    -- calculate the number of days in these compelete weeks.
    select @indDays = @intWeeks * @indDaysInWeek
    print 'Est. Days'
    print @indDays
    
    -- Get the number of days in the starting week. 
    if @indDaysInWeek = 5

        -- If Saturday, Sunday is holiday
        if datepart( dw, @dtStartDate) = 7
            select @intSdays = 7 - datepart( dw, @dtStartDate)
        else
            select @intSdays = 7 - datepart( dw, @dtStartDate) - 1
    else
        -- If Sunday is only <st1:place>Holiday</st1:place>
        select @intSdays = 7 - datepart( dw, @dtStartDate) 
    print 'Starting Days'
    print @intSdays
    -- Calculate the days in the last week. 
    if @indDaysInWeek = 5
        if datepart( dw, @dtEndDate) = 7
            select @intEdays = datepart( dw, @dtEndDate) - 2
        else
            select @intEdays = datepart( dw, @dtEndDate) - 1
    else
        select @intEdays = datepart( dw, @dtEndDate) - 1
    print 'End Days' 
    print @intEdays
    -- Sum everything together.
    select @indDays = @indDays + @intSdays + @intEdays
    print 'Ans'
    print @indDays
end

Note: Starting date is Exclusive.

Here if you notice, if the number of working days is 6, we need not worry about any thing, we can simply count the days. If the number of working days is 5 then we have to take care of Saturday.

That’s it. It’s simple.

C# implementation

OK, but let’s say if you calculate this on your presentation layer, the stored procedure or “function” will not work. So now we will implement the same algorithm in C#.

Here is the implementation in C#:

/// <summary>
/// Calulates Business Days within the given range of days.
/// Start date and End date inclusive.
/// </summary>
/// <param name="startDate">Datetime object 
/// containing Starting Date</param>
/// <param name="EndDate">Datetime object containing 
/// End Date</param>
/// <param name="NoOfDayWeek">integer denoting No of Business 
/// Day in a week</param>
/// <param name="DayType"> DayType=0 for Business Day and 
/// DayType=1 for WeekEnds </param>
/// <returns></returns>

public static double CalculateBDay(
    DateTime startDate, 
    DateTime EndDate, 
    int NoOfDayWeek, /* No of Working Day per week*/
    int DayType 
)
{
    double iWeek, iDays, isDays, ieDays;
    //* Find the number of weeks between the dates. Subtract 1 */
    // since we do not want to count the current week. * /
    iWeek =DateDiff("ww",startDate,EndDate)-1 ;
    iDays = iWeek * NoOfDayWeek;
    //
    if( NoOfDayWeek == 5)
    {
        //-- If Saturday, Sunday is holiday
        if ( startDate.DayOfWeek == DayOfWeek.Saturday ) 
            isDays = 7 -(int) startDate.DayOfWeek;
        else
            isDays = 7 - (int)startDate.DayOfWeek - 1;
    } 
    else
    {
        //-- If Sunday is only <st1:place>Holiday</st1:place>
        isDays = 7 - (int)startDate.DayOfWeek;
    }
    //-- Calculate the days in the last week. These are not included in the
    //-- week calculation. Since we are starting with the end date, we only
    //-- remove the Sunday (datepart=1) from the number of days. If the end
    //-- date is Saturday, correct for this.
    if( NoOfDayWeek == 5)
    {
        if( EndDate.DayOfWeek == DayOfWeek.Saturday ) 
            ieDays = (int)EndDate.DayOfWeek - 2;
        else
            ieDays = (int)EndDate.DayOfWeek - 1;
    }
    else
    {
        ieDays = (int)EndDate.DayOfWeek - 1 ;
    }
    //-- Sum everything together.
    iDays = iDays + isDays + ieDays;
    if(DayType ==0) 
        return iDays;
    else
        return T.Days - iDays; 
}

By products

• DateDiff function

  While I was working on this issue, I also came across another issue with DateTime functions in C#. I came to know that C# does not have important functions like DateDiff, found in VB.NET. So I have included that in the same library. As Tim McCurdy said we can include Microsoft.VisualBasic.dll to our project and use DateDiff function implemented by the VB team, but I have noticed many people don't like the idea of mixing C# code with VB.NET code, though it is technically perfectly fine. Second problem with calculation of week, month or year is, they are not simple. You can not get the number of weeks = TimeSpan.Totaldays / 7. The rule says, number of week equals to number of time you cross the week boundary for given duration. To solve this problem I have added a new function called GetWeeks.

  C#

  /// <summary>
  /// Calculate weeks between starting date and ending date
  /// </summary>
  /// <param name="stdate"></param>
  /// <param name="eddate"></param>
  /// <returns></returns>
  
  public static int GetWeeks(DateTime stdate, DateTime eddate )
  {
  
    TimeSpan t= eddate - stdate;
    int iDays;
  
    if( t.Days < 7)
    {
      if(stdate.DayOfWeek > eddate.DayOfWeek)
        return 1; //It is accross the week 
  
      else
        return 0; // same week
    }
    else
    {
      iDays = t.Days -7 +(int) stdate.DayOfWeek ;
      int i=0;
      int k=0;
  
      for(i=1;k<iDays ;i++)
      {
        k+=7;
      }
  
      if(i>1 && eddate.DayOfWeek != DayOfWeek.Sunday ) i-=1; 
        return i;
    } 
  }
  
  
  /// <summary>
  /// Mimic the Implementation of DateDiff function of VB.Net.
  /// Note : Number of Year/Month is calculated
  ///        as how many times you have crossed the boundry.
  /// e.g. if you say starting date is 29/01/2005
  ///        and 01/02/2005 the year will be 0,month will be 1.
  /// 
  
  /// </summary>
  /// <param name="datePart">specifies on which part 
  ///   of the date to calculate the difference </param>
  /// <param name="startDate">Datetime object containing
  ///   the beginning date for the calculation</param>
  /// <param name="endDate">Datetime object containing
  ///   the ending date for the calculation</param>
  /// <returns></returns>
  
  public static double DateDiff(string datePart, 
                DateTime startDate, DateTime endDate)
  {
  
    //Get the difference in terms of TimeSpan
    TimeSpan T;
  
    T = endDate - startDate;
  
    //Get the difference in terms of Month and Year.
    int sMonth, eMonth, sYear, eYear;
    sMonth = startDate.Month;
    eMonth = endDate.Month;
    sYear = startDate.Year;
    eYear = endDate.Year; 
    double Months,Years=0;
    Months = eMonth - sMonth;
    Years = eYear - sYear;
    Months = Months + ( Years*12);
  
    switch(datePart.ToUpper())
    {
      case "WW":
      case "DW":
           return (double)GetWeeks(startDate,endDate);
      case "MM":
           return Months;
      case "YY":
      case "YYYY":
           return Years;
      case "QQ":
      case "QQQQ":
           //Difference in Terms of Quater
           return Math.Ceiling((double)T.Days/90.0);
      case "MI":
      case "N":
           return T.TotalMinutes ;
      case "HH":
           return T.TotalHours ;
      case "SS":
           return T.TotalSeconds;
      case "MS":
           return T.TotalMilliseconds;
      case "DD":
      default:
           return T.Days; 
    }
  }

• Age calculation

  This is a simple calculation compared to business days. I have added the function that can calculate the age on a specified date, in terms of year, month and days:

  C#

  /// <summary>
  /// Calculate Age on given date.
  /// Calculates as Years, Months and Days.
  /// </summary>
  /// <param name="DOB">Datetime object 
  /// containing DOB value</param>
  /// <param name="OnDate">Datetime object containing given 
  /// date, for which we need to calculate the age</param>
  /// <returns></returns>
  
  public static string Age(DateTime DOB, DateTime OnDate)
  {
      //Get the difference in terms of Month and Year.
      int sMonth, eMonth, sYear, eYear;
      double Months, Years;
      
      sMonth = DOB.Month;
      eMonth = OnDate.Month;
      sYear = DOB.Year;
      eYear = OnDate.Year; 
      
      // calculate Year
      if( eMonth >= sMonth) 
          Years = eYear - sYear;
      else
          Years = eYear - sYear -1;
          
      //calculate Months
      if( eMonth >= sMonth) 
          Months = eMonth - sMonth;
      else
          if ( OnDate.Day > DOB.Day)
              Months = (12-sMonth)+eMonth-1;
          else
              Months = (12-sMonth)+eMonth-2;
      
      double tDays=0;
      
      //calculate Days
      if( eMonth != sMonth && OnDate.Day != DOB.Day ) 
      {
          if(OnDate.Day > DOB.Day) 
          tDays = DateTime.DaysInMonth(OnDate.Year, 
                                     OnDate.Month) - DOB.Day; 
          else
          tDays = DateTime.DaysInMonth(OnDate.Year, 
                      OnDate.Month-1) - DOB.Day + OnDate.Day ; 
      }
      string strAge = Years+"/"+Months+"/"+tDays; 
      return strAge;
  }

Summary

If you notice the algorithm, I have talked about holidays too, but I have not implemented this in any of the above code but that can be taken care of by a simple query. So I am leaving that up to you. Do let me know if you like this code, or if it was useful in your project. You can mail me at Gaurang.Desai@gmail.com.

History

• Bug fixed for calculation of weeks for given duration in C# code. 17^th Aug, 2005.
• First release on 10^th Jun, 2005.