Poor Performance For Business Day Calculation From Aspfaq Sample

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 )