none
Number Of Business Days

    Question

  • 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

    Tuesday, October 17, 2006 7:51 PM

Answers

  • 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.
    Tuesday, October 17, 2006 11:02 PM

All replies

  • 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

    Tuesday, October 17, 2006 8:08 PM
    Moderator
  • My apologies.  Standard business days as viewed by most American businesses.  Mon-Fri excluding holidays where businesses would typically be closed.

    Tuesday, October 17, 2006 8:15 PM
  • 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

     

    Tuesday, October 17, 2006 8:33 PM
    Moderator
  • 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.
    Tuesday, October 17, 2006 8:38 PM
  • Fair enough; sorry for being too rammy.

    Dave

    Tuesday, October 17, 2006 9:42 PM
    Moderator
  • 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.
    Tuesday, October 17, 2006 11:02 PM
  • 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

    Wednesday, October 18, 2006 8:25 PM
    Moderator
  • 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)
    )

     

    Tuesday, April 10, 2007 7:51 PM