Number Of Business Days

Number Of Business Days

• jtallard

  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)
  )
  

  
  

• dragoncells

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

  
  

• Rajesh Ladda

  Fair enough; sorry for being too rammy.

  Dave

  
  

• Jonathan Lavon

  Man.

  Is this list of holidays the ones that you mean

  • New Year's Day
  • President's Day (the Monday)
  • Memorial Day (the Monday)
  • Independence Day
  • Labor Day (the Monday)
  • Thanksgiving Day
  • Christmas

  
  

• Promit

  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

  
  

• pigi76

  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.
  

• Todd Biggs - Windows Live

  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

  
  

• ssfftt

  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.
  
  
• Number Of Business Days