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
- 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.
- 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
- Calculate the number of time span in terms of weeks. Call it, W.
- Deduct the first week from the number of weeks. W= W-1
- Multiply the number of weeks with the number of working days per week. Call it, D.
- Find out the holidays during the specified time span. Call it, H.
- Calculate the days in the first week. Call it, SD.
- Calculate the days in the last week. Call it, ED.
- 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
select @intWeeks = datediff( week, @dtStartDate, @dtEndDate) - 1
print 'week'
print @intWeeks
select @indDays = @intWeeks * @indDaysInWeek
print 'Est. Days'
print @indDays
if @indDaysInWeek = 5
if datepart( dw, @dtStartDate) = 7
select @intSdays = 7 - datepart( dw, @dtStartDate)
else
select @intSdays = 7 - datepart( dw, @dtStartDate) - 1
else
select @intSdays = 7 - datepart( dw, @dtStartDate)
print 'Starting Days'
print @intSdays
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
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#:
public static double CalculateBDay(
DateTime startDate,
DateTime EndDate,
int NoOfDayWeek,
int DayType
)
{
double iWeek, iDays, isDays, ieDays;
iWeek =DateDiff("ww",startDate,EndDate)-1 ;
iDays = iWeek * NoOfDayWeek;
if( NoOfDayWeek == 5)
{
if ( startDate.DayOfWeek == DayOfWeek.Saturday )
isDays = 7 -(int) startDate.DayOfWeek;
else
isDays = 7 - (int)startDate.DayOfWeek - 1;
}
else
{
isDays = 7 - (int)startDate.DayOfWeek;
}
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 ;
}
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.
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;
else
return 0;
}
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;
}
}
public static double DateDiff(string datePart,
DateTime startDate, DateTime endDate)
{
TimeSpan T;
T = endDate - startDate;
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":
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:
public static string Age(DateTime DOB, DateTime OnDate)
{
int sMonth, eMonth, sYear, eYear;
double Months, Years;
sMonth = DOB.Month;
eMonth = OnDate.Month;
sYear = DOB.Year;
eYear = OnDate.Year;
if( eMonth >= sMonth)
Years = eYear - sYear;
else
Years = eYear - sYear -1;
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;
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. 17th Aug, 2005.
- First release on 10th Jun, 2005.
