How many work days?

Here's a fun script I got asked to write to calculate the number of work days between a given start and end date, including those dates.

The query does not exclude holidays, only Saturdays and Sundays are removed from the count. It would be easy enough to exclude holidays by hooking a CASE up to a table that contains date records for the holidays your business has declared non-work days. SQL does NOT know that Mardi Gras is a holiday for your south Louisiana company, folks. :)

It also determines the value of your DATEFIRST setting and sets it if it is not default. This is only a session-wide declaration.

I welcome any feedback.

Again, I know, blogspot doesn't have a way to format this better.

IF @@DATEFIRST <> 7
SET DATEFIRST 7

declare @startdate smalldatetime
, @enddate smalldatetime
, @workdays int


select @startdate = '1/1/2010'
, @enddate = '1/17/2010'

select @workdays =

--Raw number of days including both the start and end dates.
datediff(d, @startdate, @enddate) + 1

--Is the start date a weekend?
- CASE WHEN
datepart(dw, @startdate) in (1, 7)
THEN
1
ELSE 0
END

--Is End Date a weekend?
- CASE WHEN
datepart(dw, @enddate) in (1, 7)
THEN
1
ELSE 0
END

--Remove whole week weekends.
-
CASE WHEN
datediff(d, @startdate, @enddate)>7
THEN
CASE WHEN
datediff(d, @startdate, @enddate)/7 > 0
THEN (datediff(d, @startdate, @enddate)/7) * 2
ELSE 0
END
ELSE 0
END

-- Remove a weekend from an incomplete week
-
CASE WHEN
datediff(d, @startdate, @enddate)%7 > 0
THEN
CASE WHEN
datepart(dw, @startdate) + (datediff(d, @startdate, @enddate)%7) = 8
THEN 1
WHEN
datepart(dw, @startdate) + (datediff(d, @startdate, @enddate)%7) > 8
THEN 2
ELSE 0
END
ELSE 0
END


SELECT @workdays

"Measure not the work until the day's out and the labor done."-Elizabeth Barrett Browning