Simple 'WHERE' clause question

Question

Simple 'WHERE' clause question

smallmoney

SSCarpal Tunnel

Points: 4378
More actions
July 28, 2009 at 12:36 pm

#136616

SELECTmeetingstartdate,
GETDATE() AS today,
(DATEDIFF(dd, GETDATE(), meetingstartdate)) AS numberofdays
FROM EventMeetingSetup
WHEREmeetingstartdate > GETDATE()
--question below
AND (DATEDIFF(dd, GETDATE(), meetingstartdate)) <= 90
--how come i can't use numberofdays <= 90 instead of the equation above[/code]

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

drew.allen SSC Guru Points: 76985 More actions · Answer 1

Because SQL doesn't process the statements from the top to bottom. Check out this website for more information http://blog.sqlauthority.com/2009/04/06/sql-server-logical-query-processing-phases-order-of-statement-execution/. The WHERE clause is processed before the SELECT clause, so it can't refer to aliases defined in the SELECT clause.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

smallmoney SSCarpal Tunnel Points: 4378 More actions · Answer 2

Awwww. Just something I always wondered about for the past 2 years but never asked.

Thanks for the link

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

I'd also make the following change to your where clause so that the Query Optimizer can make use of an index on meetingstartdate if it exists.

SELECT

meetingstartdate,

GETDATE() AS today,

(DATEDIFF(dd, GETDATE(), meetingstartdate)) AS numberofdays

FROM

EventMeetingSetup

WHERE

meetingstartdate > GETDATE()

--question below

-- AND (DATEDIFF(dd, GETDATE(), meetingstartdate)) <= 90

AND meetingstartdate <= dateadd(dd, 90, getdate())

--how come i can't use numberofdays <= 90 instead of the equation above

smallmoney SSCarpal Tunnel Points: 4378 More actions · Answer 4

Lynn Pettis (7/28/2009)
I'd also make the following change to your where clause so that the Query Optimizer can make use of an index on meetingstartdate if it exists.
SELECT
meetingstartdate,
GETDATE() AS today,
(DATEDIFF(dd, GETDATE(), meetingstartdate)) AS numberofdays
FROM
EventMeetingSetup
WHERE
meetingstartdate > GETDATE()
--question below
-- AND (DATEDIFF(dd, GETDATE(), meetingstartdate)) <= 90
AND meetingstartdate <= dateadd(dd, 90, getdate())
--how come i can't use numberofdays <= 90 instead of the equation above

There isn't an index on that column but will your code run faster anyways?

That's awesome how you modified the where clause, I didn't think of it like that.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Looking at the query and the fact that it uses just that one column, I'd add an index on that column. If it has to do a table scan either way it will probably run just as fast either way.