SQL Stored Procedure: Business Hours

Question

How can I create a stored procedure that accepts a start and end date.(e.g April 1 - April 30

1.) Get the business days including Saturdays x (a value). +
2.) Get Holidays x (a value)

and return the total.

I'm new to this, I guess it would be a tsql function. hmm.

any help would be appreciated.

Thanks

Answer 1

http://classicasp.aspfaq.com/date-time-routines-manipulation/how-do-i-count-the-number-of-business-days-between-two-dates.html

Answer 2

The simplest solution to this problem is to create a Calendar table that contains a value for every day you might want to consider. You could then add columns that indicate whether it is a business day or a holiday. With that, the problem becomes trivial:

Select ..
From Calendar
Where IsBusinessDay = 1
   And Calendar.[Date] Between '2010-04-01' And '2010-04-30'

If you wanted the count of days, you could then do:

Select Sum( Case When IsBusinessDay = 1 Then 1 Else 0 End ) As BusinessDayCount
    , Sum( Case When IsHoliday = 1 Then 1 Else 0 End ) As HolidayCount
From Calendar
Where Calendar.[Date] Between '2010-04-01' And '2010-04-30'

Answer 3

First, you will need to store all of the holidays into an independant table (Christmas, Easter, New Year Day, etc. with their respective dates (normally timed at midnight));

Second, you will have to generate, into a temporary table maybe, the dates of the office days, it then excludes the dates contained in the Holidays table.

Third, you may set the office hours to these dates depending on what day it is, if you have different working hours on different day.

That is the algorithm for you to find the appropriate code implementation.

Let me know if this helps!