Click here to Skip to main content
15,896,153 members
Articles / Database Development / SQL Server

Optimized Calculation Algorithm for Business Days

Rate me:
Please Sign up or sign in to vote.
3.90/5 (18 votes)
7 Sep 20055 min read 185.9K   1.3K   59  
Library that implements business days and age calculation and also implements the DateDiff function in C#.
alter procedure SpBusinessDays (@dtStartDate datetime, @dtEndDate datetime,
@indDaysInWeek int)
as
begin
/*
Description:
   Function designed to calculate the number of business days 
between two dates.
*/
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 week.
	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 Holiday
	   select @intSdays = 7 - datepart( dw, @dtStartDate) 

	print 'Starting Days'
	print @intSdays
	-- 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 @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

	if datepart( dw, @dtStartDate) <> 7 and @indDaysInWeek = 5
	  select @indDays = @indDays + 1
	print 'Ans'
	print  @indDays

end

-- exec SpBusinessDays '05/15/2005','05/21/2005',6

/*
  exec SpBusinessDays '05/13/2005','05/23/2005',5
  select datepart( dw, '05/13/2005')
  select datediff( mm, '05/18/2005', '05/31/2006')
*/

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


Written By
Architect
United States United States
Currently he is working as Senior Soft. Engineer at Cognizant Technology Solution.He is involved in various project activities like System Architecture, Design, and Development. He is fond of conduction training for various technologies. He has have worked on various language and platforms. He is Microsoft and Oracle Certified professional. He is spending quantity and quality time in .Net world. He had also spoiled his hand with java, too.
If work is not demanding, he spends good time with his wife, Purvi.He
blogs
at WebDevs.com.

Comments and Discussions