Calculate Business Days??

Anon

Anyone know how to calculate the number of business days (in SQL) between two dates?

I have a system with a routing table that has a start date and an end date. I need to figure the number of business days between the two.

Thanks to anyone who can enlighten me!
DC

Anon

I'm not so sure sql can do this easily, but I'm sure a stored procedure or a really funky view might pull it off. I'd do it in PHP first to prototype it, then probably just use that if it's fast enough.

First, do we count holidays? Can a start or end date fall on a weekend?

Here's the basic mathod:

Count all the raw days between now and then. divide by 7, take the integer you get back and multiply by 5. For instance, let's say the first date is March 11 2000, and the second date is Sep 20 2000.

We can use mktime to find the difference in the two dates:

$time1=mktime(0,0,0,3,11,2000); # mar 11
$time2=mktime(0,0,0,9,20,2000); # sep 18
$julian1=date("z",$time1);
$julian2=date("z",$time2);
print $julian2-$julian1;

outputs 191

will give us the raw number of days between two dates. Next we find the number of weeks. Each whole week consisted of 5 business days minus whatever holidays there were. We'll subtract holidays later, first, we'll divide 193/7 and take the integer portion for now. That gives us 27.3, and at 5 days for each that's 135 days. Now on to the fractional portion.

What we need to know now is which days of the week did the begin and end dates fall on, and if a weekend fell in between them.

We can use the date() functions "w" formatting string to get the day of the week like so:

$dow1 = date("w",$time1);
$dow2 = date("w",$time2);

'0' is sunday, 6 is saturday.

If the second day of the week ($dow2) comes after the first, we just force a 0 (sunday) in $dow1 up to 1 (monday) and a 6 (saturday) in the second $dow2 to be 5 (Friday) and subtract.

If $dow2 comes earlier in the week than $dow1, then we know that a weekend comes in between them. Just add 7 to the $dow2, then subtract each other, and if either date is a weekend, subract one more day for each that is.

Next subtract out the holidays and three day weekends and you've got your answer.

Anon

check out chapter 4 of Joe Celko's SQL FOR SMARTIES (isbn 1-55860-576-2)

Anon

This seems to have done it!
Thanks!!!!!!!!!!!!!!!!!!!!!

Anon

hi,
I was wondering if the two dates fall in different year, is it the algorithm will produce correct value? Please comment.

Anon

Thanks S. Marlowe! This algorithm does indeed work for dates falling on different years. I'm not sure for dates in different milleniums, but analysis of the algorithm indicates that it should work for just about any year after the first millenium (besides, I'm not concerned with dates in different milleniums). Anyway, I've implemented the algorithm in a sotred procedure and tested with dates falling on different years, including leap year, and it works!

-F

Anon

Glad it worked. I'd forgotten I'd written that little letter so long ago... :⁾

Anon

Here's some code that I came across on the web, implemented as user defined function.

IF object_id('dbo.fn_GetBusinessDays') IS NOT NULL
DROP FUNCTION dbo.fn_GetBusinessDays
GO

/
$Header: /InterAct/Production Fixes/Code/Database/Functions/11213_fn_GetBusinessDays.sql 1 9/26/01 2:26p $
* $Revision: 1 $
-- =======================================================================================================================
Purpose: Function designed to calculate the number of business days between two dates (@start and @end)

Input: @start: beginning date for period
@end: end date for period

Output: Integer result

Example: select dbo.fn_GetBusinessDays ('2000-01-01', '2000-12-31')
-- =======================================================================================================================
*/

CREATE FUNCTION dbo.fn_GetBusinessDays (
@start DATETIME,
@end DATETIME)
RETURNS INT
AS
BEGIN
DECLARE @wks INT, -- number of full weeks in period
@days INT, -- number of business days in period (RETURNED AS RESULT)
@sdays INT, -- number of business days in starting week
@edays INT -- number of business days in ending week

-- Find the number of weeks between the dates. Subtract 1 since we do not 
-- want to count the current week.
SELECT	@wks = datediff(WEEK,@start,@end) - 1

-- calculate the number of business days in these full weeks.
SELECT	@days = @wks * 5

-- Get the number of days in the week of the start date. This is the days
-- between Saturday (datepart = 7) and the startdate. We also remove the
-- Sunday (datepart = 1). If the first day is a Saturday, do not exclude
-- this twice.
IF datepart(dw,@start) = 7
	SELECT @sdays = 7 - datepart(dw,@start)
ELSE
	SELECT @sdays = 7 - datepart(dw,@start) - 1

-- Calculate the days in the last week. These are not included in the
-- week calculation. Since we are starting with the end date, we only
-- remove the Sunday (datepart=1) from the number of days. If the end
-- date is Saturday, correct for this.
IF datepart(dw,@end) = 7
	SELECT @edays = datepart(dw,@end) - 2
ELSE
	SELECT @edays = datepart(dw,@end) - 1

-- Sum everything together and return result.
SELECT	@days = @days + @sdays + @edays
RETURN	@days

END
GO

GRANT EXEC ON dbo.fn_GetBusinessDays TO mvx_webprod
GO

gargsuchi

can u forward me that code snippet please. I'm in dire needs

Suchi Garg