Slow SQL Query - Case in Where Stmnt

[shootsie]

Hi, a query of mine slowed down significantly when this statement was
added into the where:

(DATEDIFF(day, Col_StartDate, GETDATE()) BETWEEN 1 AND
(SELECT CASE datepart(dw, getdate())
WHEN 1 THEN 2
WHEN 2 THEN 3
ELSE 1
END) )

What it is supposed to do is get Friday, Saturday and Sunday's data if
today is Monday -- in addition if the day is Sunday get Friday and
Saturdays data. Otherwise, just get yesterdays data. This works,
however it slowed down the query by 12X. I think it may be the use of
a "case" because if I hard code it there isn't a problem. Any
suggestions for alternatives?

[Thomas R. Hummel]

You didn't mention if there was any indexing on Col_StartDate.
Actaully, you didn't mention much of anything... but you may want to
try moving Col_StartDate out of the DATEDIFF function. Something like
the following:

SET DATEFIRST 7 -- Always explicitly set this when using DATEPART w/
dw

DECLARE @start_date DATETIME, @end_date DATETIME

-- Set end date to midnight this morning
SET @end_date = CAST(CONVERT(CHAR(10), GETDATE(), 112) AS DATETIME)

SET @start_date = CASE DATEPART(dw, GETDATE())
WHEN 1 THEN DATEADD(dd, -2, @end_date)
WHEN 2 THEN DATEADD(dd, -3, @end_date)
ELSE DATEADD(dd, -1, @end_date)
END

SELECT ...
WHERE Col_StartDate BETWEEN @start_date AND @end_date

Since BETWEEN is inclusive, you may need to change it to < and >= the
start and end dates if you have data that has datetime values of
exactly midnight for those days.

HTH,
-Tom.

[shootsie]

Thanks for your help! To be honest, I'm not sure how this speeded it
up, but I put the case statement in a user defined function and that
did the trick!