June 29, 2012 at 1:23 pm
I have the following SQL, but it's returning the error "Invalid column name 'ArrivalOrAdmitDateTime'". From what I can tell with the data, the ArrivalDateTime, is sometimes null. Which ends up being the else in the case statement. So is the DATEDIFF calc bombing because of that? Or what else is it?
select A.*, CASE
WHEN ArrivalDateTime IS NULL
THEN AdmitDateTime
ELSE ArrivalDateTime
END AS ArrivalOrAdmitDateTime
From AbstractData A
Where DATEDIFF(dd, ArrivalOrAdmitDateTime, DischargeDateTime) < 2
June 29, 2012 at 1:30 pm
select A.*, CASE
WHEN ArrivalDateTime IS NULL
THEN AdmitDateTime
ELSE ArrivalDateTime
END AS ArrivalOrAdmitDateTime
From AbstractData A
Where DATEDIFF(dd, ArrivalOrAdmitDateTime, DischargeDateTime) < 2
You can not use the alias name in where clause..
do the following
--below should work...
select * from (
select A.*, CASE
WHEN ArrivalDateTime IS NULL
THEN AdmitDateTime
ELSE ArrivalDateTime
END AS ArrivalOrAdmitDateTime
From AbstractData A
) dta
Where DATEDIFF(dd, ArrivalOrAdmitDateTime, DischargeDateTime) < 2
---or this will work as well.I will prefer the first one though..That looks more simpler and clearer
select A.*, CASE
WHEN ArrivalDateTime IS NULL
THEN AdmitDateTime
ELSE ArrivalDateTime
END AS ArrivalOrAdmitDateTime
From AbstractData A
) dta
Where DATEDIFF(dd, CASE
WHEN ArrivalDateTime IS NULL
THEN AdmitDateTime
ELSE ArrivalDateTime
END
, DischargeDateTime) < 2
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
June 29, 2012 at 1:47 pm
AWESOME - thank you I think I got it to work!!!!!!!!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply