July 28, 2009 at 12:36 pm
SELECT meetingstartdate,
GETDATE() AS today,
(DATEDIFF(dd, GETDATE(), meetingstartdate)) AS numberofdays
FROM EventMeetingSetup
WHERE meetingstartdate > GETDATE()
--question below
AND (DATEDIFF(dd, GETDATE(), meetingstartdate)) <= 90
--how come i can't use numberofdays <= 90 instead of the equation above[/code]
July 28, 2009 at 12:44 pm
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
July 28, 2009 at 12:57 pm
Awwww. Just something I always wondered about for the past 2 years but never asked.
Thanks for the link
July 28, 2009 at 12:57 pm
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
July 28, 2009 at 1:08 pm
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.
July 28, 2009 at 2:00 pm
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.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply