OK, so I really never need a reason. The question ofter arises.
“How can I count the number of days between 2 dates, but exclude weekends and holidays”
EDIT: In this thread, Dr. Cross Join(aka Jeff Smith) posts a method where you don't need to add weekends. I gotta test it out yet, but I pretty sure Jeff's right.
Here's How
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