Calculate # of work days ?

kscase · May 24th, 2007, 04:03 PM

I need to calculate the number of business work days between the date_insert and today.
This value will be re-calculated each Monday.
So, I'm guessing that the best way to do this will be a stored procedure.

Has any one else done this?
Any ideas?

Thanks,
Karen

dparsons · May 24th, 2007, 04:11 PM

I have no experience doing this, but this MSDN article should set you along the right path
http://msdn2.microsoft.com/en-us/library/aa224838(sql.80).aspx

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
Technical Editor for: Professional Search Engine Optimization with ASP.NET
http://www.wiley.com/WileyCDA/WileyT...470131470.html
================================================== =========
Why can't Programmers, program??
http://www.codinghorror.com/blog/archives/000781.html
================================================== =========

Jeff Moden · June 1st, 2007, 08:49 PM

This should help... doesn't take Holidays into account...

Code:

SELECT
   (DATEDIFF(dd, @StartDate, @EndDate) + 1)
  -(DATEDIFF(wk, @StartDate, @EndDate) * 2)
  -(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)
  -(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END)

--Jeff Moden

kscase · June 9th, 2007, 09:11 PM

Thanks for the help.

I was told yesterday the client decided to do aging based on calendar days instead!

Thanks again.
Karen