Number Of Business Days

Hello,

Without creating an additional table and with the least amount of code, I'm looking for a way to determine the number of business days in a given month.

Thanks

Synergy:

Your question contains a huge amount of subjectivity. What is your view of what constitutes a business day? Do you mean every Monday through Sunday? Exclude New Years day? Ground Hogs day? More information in this case is necessary.

Dave

|||

My apologies. Standard business days as viewed by most American businesses. Mon-Fri excluding holidays where businesses would typically be closed.

|||

Man.

Is this list of holidays the ones that you mean?

New Year's Day|||Let's start by clarifying that my name is not 'man'. Though I appreciate your willingness to help, it seems you are unable to do so without having every single detail given to you. Possibly I plan to edit the code depending on what holidays are observed by a particular organization. Unless you have a function tucked in your back pocket which automatically excludes certain holidays, I don't see that it matters which ones are used for example purposes. If you do have such a function, we can use whatever holidays it uses.|||

Fair enough; sorry for being too rammy.

Dave

|||Best way is to generate a calendar table (you can generate the data in Excel) and represent attributes like holidays, week days etc. Then you can answer your question with a simple query. You can write a scalar UDF that has the logic to eliminate holidays and weekends in a month but that is going to be cumbersome to maintain. For example, government organizations observe different set of holidays than companies. So if you have an application that is deployed in different environments you will have to special case lot of stuff. If you go with the calendar table approach then it is easy to maintain different types of calendars, attributes and keep your queries simple. Search the web for "calendar table" and you will find lot of examples.|||

Synergy:

I agree with Umachandar. I have implemented a number of business day calculation functions with all of them based on a calendar table. I re-tooled a function I once messed around with. I think it performs rather mediocre, but you really seemed to be asking for a starting point and this routine might be of some value.

Dave

alter function dbo.businessDays
( @.arg_month integer,
@.arg_year integer
)
returns integer
as

begin

if @.arg_month is null
or @.arg_month > 12
or @.arg_month < 1
or @.arg_year is null
or @.arg_year < 1
return (null)

declare @.yr varchar (4) set @.yr = convert(varchar(4), @.arg_year)
declare @.mo varchar (2) set @.mo = convert(varchar(2),@.arg_month)
declare @.busDays integer set @.busDays = 20

if isDate ( @.mo + '/1/' + @.yr ) = 0
return (null)

--
-- If it is February and not a leap year, return the default number of
-- business days.
--
-- If President's day Monday is observed, modify to subtract another day.
--

if @.arg_month = 2
if @.arg_year % 4 <> 0
or @.arg_year % 100 = 0 and @.arg_year % 400 <> 0
return ( @.busDays )

--
-- Add in additional work days if the 29, 30 or 31 are also business days.
--

if datepart (dw, @.mo+'/29/'+@.yr) between 2 and 6
set @.busDays = @.busDays + 1

if @.arg_month = 2
return ( @.busDays )

--
-- Check the thirtieth day of the month for business.
--
-- Subtract a business day for Mondays of each of these holidays:
-- o Labor Day
-- o Thanksgiving
--

if datepart (dw, @.mo+'/30/'+@.yr) between 2 and 6
set @.busDays = @.busDays + 1

if @.arg_month = 11
or @.arg_month = 9
begin
set @.busDays = @.busDays - 1
return (@.busDays)
end

if @.arg_month = 4
or @.arg_month = 6
return (@.busDays)

--
-- Check the 31st day of the month for business.
--
-- Subtract a business day for each of these holidays:
-- o New Year's Day
-- o Memorial Day
-- o Independence Day
-- o Christmas
--

if datepart (dw, @.mo+'/31/'+@.yr) between 2 and 6
set @.busDays = @.busDays + 1

if @.arg_month = 5
begin
set @.busDays = @.busDays - 1
return (@.busDays)
end

if @.arg_month = 1
begin
if datepart (dw, @.mo+'/1/'+@.yr) between 2 and 6
begin
set @.busDays = @.busDays - 1
end
return (@.busDays)
end

if @.arg_month = 7
begin
if datepart (dw, @.mo+'/4/'+@.yr) between 2 and 6
begin
set @.busDays = @.busDays - 1
end
return (@.busDays)
end

if @.arg_month = 12
if datepart (dw, @.mo+'/25/'+@.yr) between 2 and 6
set @.busDays = @.busDays - 1

return (@.busDays)

end

|||

I am using this query to get amount of business days (days from mon to fri)

Code Snippet

set datefirst 1
declare @.sdate datetime
declare @.edate datetime

select @.sdate = '20070516' --for example, start date May, 16th
select @.edate='20070531' --end date May, 31st

select datediff(day, @.sdate, @.edate)+1-(
select (case datepart(dw, @.sdate)
when 7 then (datepart(ww, @.edate)-datepart(ww, @.sdate))*2-1
else (datepart(ww, @.edate)-datepart(ww, @.sdate))*2
end)+
(case datepart(dw, @.edate)
when 6 then 1
when 7 then 2
else 0
end)
)