quote:

---

Originally posted by setbasedisthetruepath

A simple problem, really. You need a table containing all dates you wish to exclude, likely all weekends and holidays. Compute the straight DATEDIFF() and then subtract the # of rows from your exclusion table where the date falls in your date range. There's no "one size fits all" solution b/c everyone's definition of holiday is different (government vs. corporate vs. religious, etc).

Jonathan
Gaming will never be the same

---

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