WHERE help needed - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

WHERE help needed

Author

Topic

JeffS23
Posting Yak Master

212 Posts

Posted - 2008-01-11 : 15:42:43

I am having issues in my WHERE Clause. Specific to this line:

WHERE [Last Visit Date] >= ISNULL(NULL,'1/1/1900') and
[Last Visit Date] < dateadd(d, 1,ISNULL(NULL,'1/1/3000'))

I get this:
Msg 207, Level 16, State 1, Line 44
Invalid column name 'Last Visit Date'.
Msg 207, Level 16, State 1, Line 45
Invalid column name 'Last Visit Date'.

My Full Query:

/* Patient List*/
SET NOCOUNT ON

DECLARE @Zip varchar(40)
SELECT @Zip = LTRIM(RTRIM('NULL')) + '%';
WITH cteMedlitsPatientStatus AS
(
SELECT * FROM Medlists WHERE TableName = 'PatientProfileStatus'
)

SELECT
PatientID, RespSameAsPatient=isnull(PatientSameAsGuarantor,0),
PatientName=CASE
WHEN RTRIM(pp.Last + ' ' + ISNULL(pp.Suffix,'')) <> '' THEN
RTRIM(RTRIM(pp.Last + ' ' + ISNULL(pp.Suffix,'')) + ', ' + ISNULL(pp.First,'') + ' ' + ISNULL(pp.Middle,''))
ELSE RTRIM(ISNULL(pp.First,'') + ' ' + ISNULL(pp.Middle,''))
END,
PatientAddr1=pp.Address1, PatientAddr2=pp.Address2,
PatientCity=pp.City, PatientState=pp.State, PatientZip=pp.Zip,
PatientRespName=CASE
WHEN RTRIM(pr.Last + ' ' + ISNULL(pr.Suffix,'')) <> '' THEN
RTRIM(RTRIM(pr.Last + ' ' + ISNULL(pr.Suffix,'')) + ', ' + ISNULL(pr.First,'') + ' ' + ISNULL(pr.Middle,''))
ELSE RTRIM(ISNULL(pr.First,'') + ' ' + ISNULL(pr.Middle,''))
END,
PatientRespAddr1=pr.Address1, PatientRespAddr2=pr.Address2, PatientRespCity=pr.City,
PatientRespState=pr.State, PatientRespZip=pr.Zip, FinancialClass=isnull(ml.Description,'none'),
Doctor=df.ListName,Facility=df1.OrgName,Balance=isnull(ppa.PatBalance,0)+isnull(ppa.InsBalance,0), pp.DeathDate,
Status = ml1.Description,
pp.BirthDate,
(select top 1 visit
from patientvisit pv
where pp.patientprofileid = pv.PatientProfileID
and datediff(day, getDate(), visit) < 0 order by visit desc) as [Last Visit Date]

FROM PatientProfile pp
LEFT JOIN PatientProfileAgg ppa ON pp.PatientProfileID = ppa.PatientProfileID
LEFT JOIN Guarantor pr ON pp.GuarantorID = pr.GuarantorID
LEFT JOIN MedLists ml ON pp.FinancialClassMID = ml.MedListsID
LEFT JOIN DoctorFacility df ON pp.DoctorID = df.DoctorFacilityID
LEFT JOIN DoctorFacility df1 ON pp.FacilityId = df1.DoctorFacilityID
LEFT JOIN cteMedlitsPatientStatus ml1 ON pp.PatientStatusMId = ml1.MedlistsId

WHERE
[Last Visit Date] >= ISNULL(NULL,'1/1/1900') and
[Last Visit Date] < dateadd(d, 1,ISNULL(NULL,'1/1/3000'))
AND --Filter on patient
(
(NULL IS NOT NULL AND pp.PatientProfileID IN (NULL)) OR
(NULL IS NULL)
)
AND -- Filter inactive patients
(
( 0 = 0 AND (ml1.Code <> 'I') ) OR
( 0 = 1)
)
AND -- Filter deceased patients
(
( 0 = 0 AND (ml1.Code <> 'X') ) OR
( 0 = 1)
)
AND --Filter on doctor
(
(NULL IS NOT NULL AND pp.DoctorID IN (NULL)) OR
(NULL IS NULL)
)
AND --Filter on financial class
(
(NULL IS NOT NULL AND pp.FinancialClassMId = NULL) OR
(NULL IS NULL)
)
AND --Filter on guarantor
(
(NULL IS NOT NULL AND pp.GuarantorID IN (NULL)) OR
(NULL IS NULL)
)
AND --Filter on facility
(
(NULL IS NOT NULL AND pp.FacilityID IN (NULL)) OR
(NULL IS NULL)
)
AND
(
(NULL IS NOT NULL AND 1 = 1 AND pp.zip LIKE @Zip) OR
(NULL IS NOT NULL AND 1 = 2 AND pr.zip LIKE @Zip) OR
(NULL IS NULL)
)

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-01-11 : 16:37:25

[Last Visit Date] is not in scope of your WHERE clause since it is based on a subquery in your SELECT statement.
You should either JOIN to patientvisit and then GROUP BY PatientProfileID and aggregate your other SELECT values
or
use a derived table or CTE (common table expression) to put the max visit date into an object in your FROM clause.

Be One with the Optimizer
TG

JeffS23
Posting Yak Master

212 Posts

Posted - 2008-01-11 : 16:56:14

TG -

Would you mind showing me in Syntax what you mean?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-01-11 : 17:33:44

sure:


create table #t1 (tid int, tDesc varchar(2))
create table #t2 (tid int, dt datetime)

insert #t1 values (1, 'T1')
insert #t1 values (2, 'T2')
insert #t2 values (1, getdate()-1)
insert #t2 values (1, getdate()-2)
insert #t2 values (1, getdate()-3)
insert #t2 values (2, getdate()-1)
insert #t2 values (2, getdate()-2)

--This won't work with the WHERE clause
select t1.tid
	,t1.tDesc
	,(select max(dt) [max date] from #t2 t2 where t2.tid = t1.tid)
from	#t1 t1
--where [max date] < getdate()  

--This works
select	t1.tid
	,t1.tDesc
	,max(t2.dt) [max date]
from	#t1 t1
inner join #t2 t2 on t2.tid = t1.tid
group by t1.tid
		,t1.tDesc

drop table #t2
drop table #t1

I'm not sure what your intention is behind the that WHERE clause logic.

ie: like this

AND --Filter on patient
(
(NULL IS NOT NULL AND pp.PatientProfileID IN (NULL)) OR
(NULL IS NULL)
)

and this

AND -- Filter inactive patients
(
( 0 = 0 AND (ml1.Code <> 'I') ) OR
( 0 = 1)
)

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources