Calculating # of Work Days

I have a report that has two parameters: Start Date and End Date.
I would like to calulcate the number of work days (Mon - Fri) that occur
between those two dates. Any ideas how to accomplish this?An user defined function might help. Check out this script, this counts the
number of work days including the begin and end date (if they are so).
I hope it helps...
CREATE FUNCTION work_days(@.begin_date DATETIME, @.end_date DATETIME)
AS
BEGIN
DECLARE @.aux_date DATETIME
SET @.aux_date = @.begin_date
DECLARE @.work_days INT
SET @.work_days = 0
WHILE @.aux_date <= @.end_date
BEGIN
IF DATEPART(weekday, @.aux_date) NOT IN (1,7)
SET @.work_days = @.work_days + 1
SET @.aux_date = @.aux_date + 1
END
RETURN @.work_days
END|||That's a cool script. But will it add holidays as work days?
I created a calendar table with a column bitIsHoliday. Then I can COUNT
where (bitIsWeekday = 1) AND (bitIsHoliday = 0).
http://www.aspfaq.com/show.asp?id=2519
"F. Dwarf" wrote:
> An user defined function might help. Check out this script, this counts the
> number of work days including the begin and end date (if they are so).
> I hope it helps...
> CREATE FUNCTION work_days(@.begin_date DATETIME, @.end_date DATETIME)
> AS
> BEGIN
> DECLARE @.aux_date DATETIME
> SET @.aux_date = @.begin_date
> DECLARE @.work_days INT
> SET @.work_days = 0
> WHILE @.aux_date <= @.end_date
> BEGIN
> IF DATEPART(weekday, @.aux_date) NOT IN (1,7)
> SET @.work_days = @.work_days + 1
> SET @.aux_date = @.aux_date + 1
> END
> RETURN @.work_days
> END|||The script works perfectly for counting # of work days but you are right
Alison, it does not account for holidays, which is something I didn't think
about before. I checked out the link you provided and have created a
calendar table as well.
Thanks for everyone's help.
"alison" wrote:
> That's a cool script. But will it add holidays as work days?
> I created a calendar table with a column bitIsHoliday. Then I can COUNT
> where (bitIsWeekday = 1) AND (bitIsHoliday = 0).
> http://www.aspfaq.com/show.asp?id=2519
>
> "F. Dwarf" wrote:
> > An user defined function might help. Check out this script, this counts the
> > number of work days including the begin and end date (if they are so).
> > I hope it helps...
> >
> > CREATE FUNCTION work_days(@.begin_date DATETIME, @.end_date DATETIME)
> > AS
> > BEGIN
> >
> > DECLARE @.aux_date DATETIME
> > SET @.aux_date = @.begin_date
> >
> > DECLARE @.work_days INT
> > SET @.work_days = 0
> >
> > WHILE @.aux_date <= @.end_date
> > BEGIN
> > IF DATEPART(weekday, @.aux_date) NOT IN (1,7)
> > SET @.work_days = @.work_days + 1
> > SET @.aux_date = @.aux_date + 1
> > END
> >
> > RETURN @.work_days
> >
> > END