Wow...it's been 2 months...
It seems there's a common question discussiong how to eliminate holidays (and weekends) from date ranges. And since I'm sick of doing a search to find it, I'd thought I'd post it. Also, people ask about daylight savings time. This is the same type of thing, but since it's regional, there the added twist of having to know where you are. Anyway, here's the code:
CREATE TABLE WeekEndsAndHolidays (DayOfWeekDate datetime, DayName char(3))
GO
SET NOCOUNT ON
DECLARE @FirstSat datetime, @x int
SELECT @FirstSat = '1/3/2004', @x = 1
--Add WeekEnds
WHILE @x < 52
BEGIN
INSERT INTO WeekEndsAndHolidays(DayOfWeekDate, DayName)
SELECT DATEADD(ww,@x,@FirstSat), 'SAT' UNION ALL
SELECT DATEADD(ww,@x,@FirstSat+1), 'SUN'
SELECT @x = @x + 1
END
SET NOCOUNT OFF
GO
SELECT * FROM WeekEndsAndHolidays Order by DayOfWeekDate
GO
-- Add US Holidaze
INSERT INTO WeekEndsAndHolidays(DayOfWeekDate, DayName)
SELECT '1/1/2004', 'THU' UNION ALL
SELECT '2/16/2004', 'MON' UNION ALL
SELECT '5/31/2004', 'MON' UNION ALL
SELECT '7/5/2004', 'MON' UNION ALL
SELECT '9/6/2004', 'MON' UNION ALL
SELECT '11/25/2004', 'THU' UNION ALL
SELECT '11/26/2004', 'FRI' UNION ALL
SELECT '12/24/2004', 'FRI' UNION ALL
SELECT '12/31/2004', 'FRI'
GO
SELECT * FROM WeekEndsAndHolidays Order by DayOfWeekDate
GO
-- WeekDays in May
DECLARE @Start datetime, @End datetime
SELECT @Start = '5/1/2004', @End = '6/1/2004'
SELECT DATEDIFF(dd,@Start,@End)- COUNT(*) AS WeekDays
FROM WeekEndsAndHolidays
WHERE DayOfWeekDate BETWEEN @Start AND @End
GO