DATEDIFF

I have this SQL...Why can't I use the derived column "no_of_days" in my WHER
E
logic? And how do I get around this?
Also, if we want to take in account wends, is there anyway just to filter
out and get a result table within the last 11 BUSINESS days?
SELECT GEN.loan_num,
FUN.funded,
DATEDIFF(day, FUN.funded, getdate()) AS no_of_days
FROM DMD_Data.dbo.GEN GEN
INNER JOIN DMD_Data.dbo.FUN FUN
ON GEN.file_id = FUN.file_id
WHERE no_of_days > 11Try,
...
WHERE DATEDIFF(day, FUN.funded, getdate()) > 11
-- or
select *
from (
SELECT GEN.loan_num,
FUN.funded,
DATEDIFF(day, FUN.funded, getdate()) AS no_of_days
FROM DMD_Data.dbo.GEN GEN
INNER
JOIN
DMD_Data.dbo.FUN FUN
ON GEN.file_id = FUN.file_id
) as t1
where no_of_days > 11
go
AMB
"wnfisba" wrote:

> I have this SQL...Why can't I use the derived column "no_of_days" in my WH
ERE
> logic? And how do I get around this?
> Also, if we want to take in account wends, is there anyway just to filt
er
> out and get a result table within the last 11 BUSINESS days?
> SELECT GEN.loan_num,
> FUN.funded,
> DATEDIFF(day, FUN.funded, getdate()) AS no_of_days
> FROM DMD_Data.dbo.GEN GEN
> INNER JOIN DMD_Data.dbo.FUN FUN
> ON GEN.file_id = FUN.file_id
> WHERE no_of_days > 11
>|||You could use a calendar table for this...
http://www.aspfaq.com/show.asp?id=2519
"wnfisba" <wnfisba@.discussions.microsoft.com> wrote in message
news:BBBC685C-CD70-4A9A-BA58-ACBF8AAE9F5C@.microsoft.com...
> I have this SQL...Why can't I use the derived column "no_of_days" in my
WHERE
> logic? And how do I get around this?
> Also, if we want to take in account wends, is there anyway just to
filter
> out and get a result table within the last 11 BUSINESS days?
> SELECT GEN.loan_num,
> FUN.funded,
> DATEDIFF(day, FUN.funded, getdate()) AS no_of_days
> FROM DMD_Data.dbo.GEN GEN
> INNER JOIN DMD_Data.dbo.FUN FUN
> ON GEN.file_id = FUN.file_id
> WHERE no_of_days > 11
>|||Remeber the order that queries are compiled. the WHERE clause iscompiled
before the SELECT clause so the column alias doesn't exist yet in the WHERE
clause, but since ORDER BY is compiled after SELECT you can reference a
column alias.
Query compilation starts at the FROM clause and moves down until, if
present, HAVING clause. SELECT and DISTINCT down to ORDER BY and, if
present, TOP is last.
When troubleshooting an error in a query or its results it is helpful for me
to follow the flow.
1. FROM
2. ON
3. JOIN
4. WHERE
5. GROUP BY
6. WITH CUBE or ROLLUP
7. HAVING
8. SELECT
9. DISTINCT
10. ORDER BY
11. TOP
Good luck and hope it helps.
"wnfisba" wrote:

> I have this SQL...Why can't I use the derived column "no_of_days" in my WH
ERE
> logic? And how do I get around this?
> Also, if we want to take in account wends, is there anyway just to filt
er
> out and get a result table within the last 11 BUSINESS days?
> SELECT GEN.loan_num,
> FUN.funded,
> DATEDIFF(day, FUN.funded, getdate()) AS no_of_days
> FROM DMD_Data.dbo.GEN GEN
> INNER JOIN DMD_Data.dbo.FUN FUN
> ON GEN.file_id = FUN.file_id
> WHERE no_of_days > 11
>