July 11, 2008 at 9:55 am
Comments posted to this topic are about the item Count Business Days
July 18, 2008 at 8:14 am
If you prepopulate a "calendar" table with your weekend days and holidays, you can also do a simple query as shown in the following example. Please note that the great majority of the code which follows is actually the code used to populate a table variable which on our system is a permanent table . All the work of calculating business days is done in the last three lines. (The tally table is simply a table of numbers [N] from one to a million which I always create in any db. )
Having a calendar table also lets you do some cute things such as having a column that designates whether the holiday is for U.S., Canada, or both, which lets your function take a parameter to pass to your where clause to pick the appropriate holidays. You might also want an option to NOT include Saturdays as business days, or to add 1 to the datediff. Different clients count turn times in different ways.
-----------------------------------------------------------------------------------------------------------------
-- example
-----------------------------------------------------------------------------------------------------------------
declare @start datetime
declare @end datetime
declare @weekends_holidays table (offday datetime primary key, weekday int)
select @start = '7/1/2008',@end = '7/10/2008'
-- the following query populates the table variable with some weekend days and the 4th of July
insert into @weekends_holidays
select distinct @start+N-1,datepart(dw,@start+N-1)
from tally
where N <= datediff(dd,@start,@end)
and datepart(dw,@start+N-1) in (1,7)-- living for the weekend
union all
select '7/4/2008',datepart(dw,'7/4/2008') -- three cheers for the red, white, and blue
select * from @weekends_holidays
-------------------------------------------------------------------------
-- all of the above was just to set up the following query
-- in practice, @weekends_holidays would be a permanent table
-------------------------------------------------------------------------
select datediff(dd,@start,@end) - count(*) as businessDays
from @weekends_holidays
where offday between @start and @end
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
July 18, 2008 at 10:55 am
Created the function using copy / paste / execute - no reported error then tested using the following:
DECLARE @Bdays AS INT
SET @Bdays = dbo.fn_GetBusinessDays('07/06/2008','07/27/2008')
SELECT @Bdays
Result returned 14 ..
Now counting on my fingers it is actually 15 days
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply