Greeting, below is the complete SQL taken from aspfaq.com (retrieved

from this newsgroup I believe) The query takes about two minutes to

run. Does anybody have a better set based way (sub-second response) to

determine business days?

CREATE TABLE dbo.Calendar

(

dt SMALLDATETIME NOT NULL PRIMARY KEY

CLUSTERED, -- Date value

IsWeekday BIT,

-- Is this date a weekday (M -

F)

IsHoliday BIT,

-- Is this date a holiday

Y SMALLINT,

-- Year the date falls in

FY SMALLINT,

-- Fiscal Year (needed?)

Q TINYINT,

-- Quarter date falls in

M TINYINT,

-- Numeric month of date

D TINYINT,

-- Numeric day of date

DW TINYINT,

-- Numeric DayOfWeek

(Sunda=1,Monday=2)

MonthName VARCHAR(9),

-- String name of month

DayName VARCHAR(9),

-- String name of day

W TINYINT

-- Week number

)

GO

-- Start & End Dates

DECLARE @StartDate DATETIME

DECLARE @EndDate DATETIME

SET @StartDate = DATEADD(d, -1, '20000101')

SET @EndDate = DATEADD(d, -1, '20300101')

-- Total number of dates to generate

DECLARE @Days INT

SET @Days = DATEDIFF(DAY, @StartDate, @EndDate)

-- Create temporary Numbers table

CREATE TABLE #Numbers

(

Number INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED

)

-- Insert a number into our temp table for each date to be generated

WHILE COALESCE(SCOPE_IDENTITY(), 0) <= @Days

BEGIN

INSERT #Numbers DEFAULT VALUES

END

-- Generate a date for each day in our timespan

INSERT Calendar(dt)

SELECT DATEADD(DAY, Number, @StartDate)

FROM #Numbers

WHERE Number <= @Days

ORDER BY Number

-- Remove the temporary Numbers table

DROP TABLE #Numbers

GO

-- Update other columns

UPDATE dbo.Calendar SET

IsWeekday = CASE WHEN DATEPART(DW, dt) IN (1, 7) THEN 0

ELSE 1 END,

IsHoliday = 0,

Y = YEAR(dt),

FY = YEAR(dt),

Q = CASE

WHEN MONTH(dt) <= 3 THEN 1

WHEN MONTH(dt) <= 6 THEN 2

WHEN MONTH(dt) <= 9 THEN 3

ELSE 4 END,

M = MONTH(dt),

D = DAY(dt),

DW = DATEPART(DW, dt),

MonthName = DATENAME(MONTH, dt),

DayName = DATENAME(DW, dt),

W = DATEPART(WK, dt)

-- Query in question (takes almost 2 minutes to execute and return a

value)

SELECT

C.dt

FROM

Calendar C

WHERE

C.IsWeekDay = 1

AND C.IsHoliday = 0

AND 9 = (SELECT COUNT(*) FROM Calendar C2 WHERE C2.dt >=

GETDATE() AND C2.dt <= C.dt AND C2.IsWeekDay = 1 AND C2.IsHoliday = 0 )